Home > Pl Sql > Pl Sql Error Handling Example

Pl Sql Error Handling Example

You can avoid unhandled exceptions by coding an OTHERS handler at the topmost level of every PL/SQL program. Please re-enable javascript in your browser settings. ROWTYPE_MISMATCH 06504 -6504 The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. DECLARE pe_ratio NUMBER(3,1); BEGIN SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio); COMMIT; EXCEPTION this content

ACCESS_INTO_NULL Your program attempts to assign values to the attributes of an uninitialized (atomically null) object. unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message. Consider the following example: EXCEPTION WHEN INVALID_NUMBER THEN INSERT INTO ... -- might raise DUP_VAL_ON_INDEX WHEN DUP_VAL_ON_INDEX THEN ... -- cannot catch the exception END; Branching to or from an Exception When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends, as shown in Example 11-12. https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm

VALUE_ERROR An arithmetic, conversion, truncation, or size-constraint error occurs. CASE 5: Then I deleted everything from the table 1 except the a1 = 1 and did a commit. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. If no exception has been raised, SQLCODE returns zero and SQLERRM returns the message: ORA-0000: normal, successful completion.

For internal exceptions, SQLCODE returns the number of the Oracle error. CURSOR_ALREADY_OPEN 06511 -6511 A program attempts to open an already open cursor. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program. Defining Your Own PL/SQL Exceptions PL/SQL lets you define exceptions of your own.

In the following example, you declare an exception named past_due: DECLARE past_due EXCEPTION; Exception and variable declarations are similar. If you redeclare a global exception in a sub-block, the local declaration prevails. SYS_INVALID_ROWID The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid. Informational: Messages for conditions that do not have an effect on performance or correctness, but that you might want to change to make the code more maintainable, such as dead code

Example 11-7 Anonymous Block Avoids ZERO_DIVIDE DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN pe_ratio := CASE net_earnings WHEN 0 THEN NULL ELSE stock_price / net_earnings END; However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters). If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome (for more information, see "Unhandled Exceptions"). In such cases, you must use dot notation to specify the predefined exception, as follows: EXCEPTION WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN -- handle the error END; How PL/SQL Exceptions Are Raised

In the following example, you call raise_application_error if an employee's salary is missing: CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS curr_sal NUMBER; BEGIN SELECT sal INTO curr_sal FROM emp WHERE his comment is here Whenever possible, write exception handlers for named exceptions instead of using OTHERS exception handlers. The functions SQLCODE and SQLERRM are especially useful in the OTHERS exception handler because they tell you which internal exception was raised. Separate them out for insertion. -- Trim white space from the call first. */ v_Call := TRIM(v_Call); -- First get the object handle v_Handle :=

For internal exceptions, SQLCODE returns the number of the Oracle error. news That way, an exception handler written for the predefined exception can process other errors, as Example 11-7 shows. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. If you redeclare a global exception in a sub-block, the local declaration prevails.

Regardless of the severity of the error, you want to leave the database in a consistent state and avoid storing bad data. Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. have a peek at these guys If there is no enclosing block, then: If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.

So, only an OTHERS handler can catch the exception. You can, however, declare the same exception in two different blocks. Passing a zero to SQLERRM always returns the message normal, successful completion.

Therefore, the exception handler must be in an enclosing or invoking block.

Jobs Send18 Whiteboard Net Meeting Tools Articles Facebook Google+ Twitter Linkedin YouTube Home Tutorials Library Coding Ground Tutor Connect Videos Search PL/SQL Tutorial PL/SQL - Home PL/SQL - Overview PL/SQL - Otherwise, DECODE returns the price-to-earnings ratio. In Example 11-20, the exception-handling part of the procedure has exception handlers for user-defined exception i_is_one and predefined exception ZERO_DIVIDE. In the following example, if the SELECT INTO statement raises ZERO_DIVIDE, you cannot resume with the INSERT statement: DECLARE pe_ratio NUMBER(3,1); BEGIN DELETE FROM stats WHERE symbol = 'XYZ'; SELECT price

All rights reserved. You need not declare them yourself. You might turn on all warnings during development, turn off all warnings when deploying for production, or turn on some warnings when working on a particular subprogram where you are concerned check my blog However, the same scope rules apply to variables and exceptions.

SELECT error_seq.nextval INTO v_SeqNum FROM DUAL; p_SeqNum := v_SeqNum; -- Insert first part of header info. These statements complete execution of the block or subprogram; control does not return to where the exception was raised. EXCEPTION WHEN NO_DATA_FOUND THEN ... -- Which SELECT statement caused the error? PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum CODE Oracle Code Library JOBS Find Or Post Oracle Jobs FORUM Oracle Discussion & Chat PSOUG

To handle raised exceptions, you write separate routines called exception handlers. Otherwise, you can handle them only with OTHERS exception handlers. The runtime system raises them implicitly (automatically). So, PL/SQL predefines some common Oracle errors as exceptions.

That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null. You can save the current state of the PLSQL_WARNINGS parameter with one call to the package, change the parameter to compile a particular set of subprograms, then restore the original parameter SYS_INVALID_ROWID ORA-01410 The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid. Defining Your Own PL/SQL Exceptions PL/SQL lets you define exceptions of your own.

The result was exactly the same as in case3 - everything was stored except 'bad' rows. To handle other Oracle errors, you can use the OTHERS handler. The sub-block cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label block_label.exception_name. Figure 7-1 Propagation Rules: Example 1 Text description of the illustration pls81009_propagation_rules_example1.gif Figure 7-2 Propagation Rules: Example 2 Text description of the illustration pls81010_propagation_rules_example2.gif Figure 7-3 Propagation Rules: Example 3 Text

You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number); where exception_name is the name of a previously declared That way, you can report errors to your application and avoid returning unhandled exceptions. Example 11-23 Exception Handler Runs and Execution Ends DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, salary, commission_pct FROM employees; DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_temp (employee_id, salary, commission_pct) To call RAISE_APPLICATION_ERROR, use the syntax raise_application_error(error_number, message[, {TRUE | FALSE}]); where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to

Retrieving the Error Code and Error Message In an exception handler, you can retrieve the error code with the built-in function SQLCODE. A pragma is a compiler directive that is processed at compile time, not at run time. But remember, an exception is an error condition, not a data item. Thus, the RAISE statement and the WHEN clause refer to different exceptions.