How To Check Error In Stored Procedure In Oracle
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss exception handling in oracle stored procedure example the workings and policies of this site About Us Learn more about oracle raise exception with message Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow
Oracle Predefined Exceptions
Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each
No Data Found Exception In Oracle
other. Join them; it only takes a minute: Sign up Checking for Error during execution of a procedure in oracle up vote 3 down vote favorite create or replace procedure proc_advertisement(CustomerID in Number, NewspaperID in number, StaffID in Number, OrderDate in date, PublishDate in date, Type in varchar, Status in varchar, Units in number) is begin insert into sqlerrm in oracle PMS.Advertisement(CustomerID, NewspaperID, StaffID, OrderDate, PublishDate, Type, Status, Units) values(CustomerID,NewspaperID, StaffID, OrderDate, PublishDate, Type, Status, Units); dbms_output.put_line('Advertisement Order Placed Successfully'); end; How to check for if any error has occurred during the execution of the procedure and if any error has occurred then I wish to display an error message. oracle stored-procedures share|improve this question asked Apr 29 '10 at 2:20 Maverick 329316 add a comment| 1 Answer 1 active oldest votes up vote 6 down vote accepted First of all, Oracle itself will raise an error message if any error occurs while running the procedure - for example: ORA-02291: integrity constraint (EMP.MGR_FK) violated - parent key not Found You can handle errors explicitly by writing an exception handler, but unless you do this well you are quite likely to just obfuscate the problem. For example you could simply add this (just before the END of your procedure: EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'An error occured'); But now your user won't know what kind of error, whereas before they could infer that it was that
shot at without result. —Winston Churchill Run-time errors arise from design faults, coding mistakes, hardware failures, and many other
Functions For Error Trapping Are Contained In Which Section Of A Pl/sql Block
sources. Although you cannot anticipate all possible errors, you can plan exception handling in oracle interview questions to handle certain kinds of errors meaningful to your PL/SQL program. With many programming languages, unless oracle exception when others you disable error checking, a run-time error such as stack overflow or division by zero stops normal processing and returns control to the operating system. With PL/SQL, a http://stackoverflow.com/questions/2734400/checking-for-error-during-execution-of-a-procedure-in-oracle mechanism called exception handling lets you "bulletproof" your program so that it can continue operating in the presence of errors. This chapter contains these topics: Overview of PL/SQL Runtime Error Handling Advantages of PL/SQL Exceptions Summary of Predefined PL/SQL Exceptions Defining Your Own PL/SQL Exceptions How PL/SQL Exceptions Are Raised How PL/SQL Exceptions Propagate Reraising https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/07_errs.htm a PL/SQL Exception Handling Raised PL/SQL Exceptions Tips for Handling PL/SQL Errors Overview of PL/SQL Compile-Time Warnings Overview of PL/SQL Runtime Error Handling In PL/SQL, an error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names. You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE stateme
handle errors and status changes using the SQLSTATE status variable, as well as the SQL Communications Area (SQLCA) and the WHENEVER directive. You also learn how to diagnose https://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_09err.htm problems using the Oracle Communications Area (ORACA). This chapter contains the following topics: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12041.htm The Need for Error Handling Error Handling Alternatives The SQLSTATE Status Variable Declaring SQLCODE Key Components of Error Reporting Using the SQLCA Using the SQL Communications Area (SQLCA) Getting the Full Text of Error Messages Using the WHENEVER Directive Obtaining the Text of SQL Statements Using the Oracle Communications Area (ORACA) in oracle The Need for Error Handling A significant part of every application program must be devoted to error handling. The main reason for error handling is that it allows your program to continue operating in the presence of errors. Errors arise from design faults, coding mistakes, hardware failures, invalid user input, and many other sources. You cannot anticipate all possible errors, but you can plan to exception handling in handle certain kinds of errors that are meaningful to your program. For the Pro*C/C++ Precompiler, error handling means detecting and recovering from SQL statement execution errors. You can also prepare to handle warnings such as "value truncated" and status changes such as "end of data." It is especially important to check for error and warning conditions after every SQL data manipulation statement, because an INSERT, UPDATE, or DELETE statement might fail before processing all eligible rows in a table. Error Handling Alternatives There are several alternatives that you can use to detect errors and status changes in the application. This chapter describes these alternatives, however, no specific recommendations are made about what method you should use. The method is, after all, dictated by the design of the application program or tool that you are building. Status Variables You can declare a separate status variable, SQLSTATE or SQLCODE, examine its value after each executable SQL statement, and take appropriate action. The action might be calling an error-reporting function, then exiting the program if the error is unrecoverable. Or, you might be able to adjust data or control variables and retry the
CLASS } [schema.]name]LNO PARAMETERS [parameter_name]PNO RECYC[LEBIN] [original_name]REL[EASE]REPF[OOTER]REPH[EADER]SGA SPOO[L] (Not available in iSQL*Plus) SQLCODE TTI[TLE]USER XQUERY Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. SHOW SGA requires a DBA privileged login. Terms system_variable Represents any system variable set by the SET command. ALL Lists the settings of all SHOW options, except ERRORS and SGA, in alphabetical order. BTI[TLE] Shows the current BTITLE definition. ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name] Shows the compilation errors of a stored procedure (includes stored functions, procedures, and packages). After you use the CREATE command to create a stored procedure, a message is displayed if the stored procedure has any compilation errors. To see the errors, you use SHOW ERRORS. When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. When you specify the type (function, procedure, package, package body, trigger, view, type, type body, dimension, or java class) and the name of the PL/SQL stored procedure, SQL*Plus shows errors for that stored procedure. For more information on compilation errors, see your PL/SQL User's Guide and Reference. schema contains the named object. If you omit schema, SHOW ERRORS assumes the object is located in your current schema. SHOW ERRORS output displays the line and column number of the error (LINE/COL) as well as the error itself (ERROR). LINE/COL and ERROR have default widths of 8 and 65, respectively. You can use the COLUMN command to alter the default widths. LNO Shows the current line number (the position in the current page of the display and/or spooled output). PARAMETERS [parameter_name] Displays the current values for one or more initialization parameters. You can use a string after the command to see a subset of parameters whose names include that string. For example, if you enter: SHOW PARAMETERS COUNT NAME TYPE VALUE ------------------------------ ----- ----- db_file_multiblock_read_count integer 12 spin_count integer 0 The SHOW PARAMETERS command, without any string following the command, displays all initial