Home PL/SQL Tutorial Learn Pl/SQL in a simple way. Enter your search terms Submit search form Web plsql-tutorial.com In the example below, you calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. When the exception hander raises ZERO_DIVIDE, the exception propagates immediately to the invoker. Example 11-24 Exception Handler Runs and Execution Continues DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_temp (employee_id, salary, commission_pct) VALUES (301, 2500, 0); BEGIN SELECT (salary / commission_pct) INTO sal_calc FROM employees_temp
> Pl Sql
> Pl Sql Error Handling Example
Pl Sql Error Handling Example
You can write handlers for predefined exceptions using the names in Table 11-1. For example, if you declare an exception named invalid_number and then PL/SQL raises the predefined exception INVALID_NUMBER internally, a handler written for INVALID_NUMBER will not catch the internal exception. You can find the value of this parameter by issuing SHOW PARAMETER USER_DUMP_DEST. dbms_output.put('Complete Call Stack:'); dbms_output.put(' Object Handle Line Number Object Name'); dbms_output.put_line(' ------------- ----------- -----------'); FOR v_CallRec in c_CallCur LOOP dbms_output.put(RPAD(' ' || v_CallRec.object_handle, 15)); dbms_output.put(RPAD(' http://setiweb.org/pl-sql/pl-sql-error-handling.php
Example 11-5 Naming Internally Defined Exception DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN ... But when the handler completes, the block is terminated. 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. Also, it can use the pragma EXCEPTION_INIT to map specific error numbers returned by RAISE_APPLICATION_ERROR to exceptions of its own, as the following Pro*C example shows: EXEC SQL EXECUTE /* Execute https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm
This stops normal execution of the block and transfers control to the exception handlers. NO_DATA_FOUND 01403 +100 A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. If the transaction succeeds, commit, then exit from the loop.
That lets you refer to any internal exception by name and to write a specific handler for it. Therefore, a PL/SQL block cannot handle an exception raised by a remote subprogram. Summary of Predefined PL/SQL Exceptions An internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. 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").
THEN -- handle the error WHEN OTHERS THEN -- handle all other errors END; If you want two or more exceptions to execute the same sequence of statements, list the exception Error-handling code is isolated in the exception-handling parts of the blocks. You can also treat particular messages as errors instead of warnings. https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm The stored procedure also had no error trap.
Defining Your Own PL/SQL Exceptions PL/SQL lets you define exceptions of your own. https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm name := name || TO_CHAR(suffix); END; -- sub-block ends END LOOP; END; / Result: Try #1 failed; trying again. You can, however, declare the same exception in two different blocks. Unlike variables, exceptions cannot appear in assignment statements or SQL statements.
For example, here is a procedure with unnecessary code that could be removed. http://setiweb.org/pl-sql/pl-sql-error-handling-best-practices.php You can avoid such problems by declaring individual variables with %TYPE qualifiers, and declaring records to hold query results with %ROWTYPE qualifiers. Retrieving the Error Code and Error Message: SQLCODE and SQLERRM In an exception handler, you can use the built-in functions SQLCODE and SQLERRM to find out which error occurred and to You might want to use a FOR or WHILE loop to limit the number of tries.
With some better error checking, we could have avoided the exception entirely, by substituting a null for the answer if the denominator was zero: DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. A PL/SQL block cannot catch an exception raised by a remote subprogram. navigate to this website Handling Raised PL/SQL Exceptions When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part, which is formatted as follows: EXCEPTION
But remember, an exception is an error condition, not a data item. ROWTYPE_MISMATCH ORA-06504 The rowtype does not match the values being fetched or assigned to it. END; Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.
LOGIN_DENIED 01017 -1017 A program attempts to log on to the database with an invalid username or password.
To reraise an exception, use a RAISE statement without an exception name, which is allowed only in an exception handler: DECLARE salary_too_high EXCEPTION; current_salary NUMBER := 20000; max_salary NUMBER := 10000; Raising Exceptions with the RAISE Statement PL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish processing. For more information, see "User-Defined Exceptions". So, the sub-block cannot reference the global exception unless it was declared in a labeled block, in which case the following syntax is valid: block_label.exception_name The following example illustrates the scope
If the INSERT succeeds, we exit from the loop immediately. 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 All Rights Reserved. http://setiweb.org/pl-sql/pl-sql-error-handling-procedure.php stmt := 2; -- designates 2nd SELECT statement SELECT ...
If you store the debugging information in a separate table, do it with an autonomous routine, so that you can commit your debugging information even if you roll back the work For example, an exception-handling part could have this syntax: EXCEPTION WHEN ex_name_1 THEN statements_1 -- Exception handler WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handler WHEN OTHERS THEN statements_3 -- If the exception is ever raised in that block (including inside a sub-block), it will be handled. You cannot anticipate all possible errors, but you can code exception handlers that allow your program to continue to operate in the presence of errors.
If your database operations might cause particular ORA-n errors, associate names with these errors so you can write handlers for them. (You will learn how to do that later in this ROWTYPE_MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO errors VALUES ('Error in statement ' || stmt); END; Copyright © 1996, 2002 Oracle Corporation.