Home > Pl Sql > Pl Sql Error Handling Example

Pl Sql Error Handling Example

In the exception-handling part, you can include exception handlers for both specific and unknown errors. 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 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 Example 11-11 Reraising Exception DECLARE salary_too_high EXCEPTION; current_salary NUMBER := 20000; max_salary NUMBER := 10000; erroneous_salary NUMBER; BEGIN BEGIN IF current_salary > max_salary THEN RAISE salary_too_high; -- raise exception END IF; this page

The error number and message can be trapped like any Oracle error. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions. CURSOR_ALREADY_OPEN 06511 -6511 A program attempts to open an already open cursor. User-defined You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package.

To have the enclosing block handle the raised exception, you must remove its declaration from the sub-block or define an OTHERS handler. NO_DATA_FOUND 01403 +100 It is raised when a SELECT INTO statement returns no rows. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled. Figure 10-1, Figure 10-2, and Figure 10-3 illustrate the basic propagation rules.

You can also perform a sequence of DML operations where some might fail, and process the exceptions only after the entire operation is complete, as described in "Handling FORALL Exceptions with SUBSCRIPT_BEYOND_COUNT 06533 -6533 A program references a nested table or varray element using an index number larger than the number of elements in the collection. SET SERVEROUTPUT ON; DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation might cause division-by-zero error. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.) ZERO_DIVIDE 01476 -1476 A program attempts to divide

To handle raised exceptions, you write separate routines called exception handlers. That way, an exception handler written for the predefined exception can process other errors, as the following example shows: DECLARE acct_type INTEGER := 7; BEGIN IF acct_type NOT IN (1, 2, For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL stops the assignment and raises https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

You cannot return to the current block from an exception handler. Within this handler, you can invoke the functions SQLCODE and SQLERRM to return the Oracle Database error code and message text. There are 3 types of Exceptions. nvl(l_text.count,0) LOOP dbms_output.put_line(l_text(i) ); END LOOP; dbms_output.put_line( 'error text: ' ); FOR i IN 1 ..

If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. This Site However, when an exception is raised inside a cursor FOR loop, the cursor is closed implicitly before the handler is invoked. You can, however, declare the same exception in two different blocks. Raise the user-defined exception based on a specific business rule in the execution section. 3.

However, an exception name can appear only once in the exception-handling part of a PL/SQL block or subprogram. this website Named system exceptions are: 1) Not Declared explicitly, 2) Raised implicitly when a predefined Oracle error occurs, 3) caught by referencing the standard name within an exception-handling routine. Also, a GOTO statement cannot branch from an exception handler into the current block. For example, here is a procedure with unnecessary code that could be removed.

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. If there is no handler for a user-defined exception, the calling application gets this error: ORA-06510: PL/SQL: unhandled user-defined exception Reraising a PL/SQL Exception Sometimes, you want to reraise an exception, 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. http://back2cloud.com/pl-sql/pl-sql-script-error-handling.php ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable having incompatible data type.

A specific exception handler is more efficient than an OTHERS exception handler, because the latter must invoke a function to determine which exception it is handling. However, other user-defined exceptions must be raised explicitly by RAISE statements. DECLARE Child_rec_exception EXCEPTION; PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292); BEGIN Delete FROM product where product_id= 104; EXCEPTION WHEN Child_rec_exception THEN Dbms_output.put_line('Child records are present for this product_id.'); END; / c) User-defined Exceptions Apart

That lets you refer to any internal exception by name and to write a specific handler for it.

To reraise an exception, simply place a RAISE statement in the local handler, as shown in the following example: DECLARE out_of_balance EXCEPTION; BEGIN ... See Also: Example 5-38, "Collection Exceptions" You can also perform a sequence of DML operations where some might fail, and process the exceptions only after the entire operation is complete, as In Example 11-13, the INSERT statement might raise an exception because of a duplicate value in a unique column. Errors can also occur at other times, for example if a hardware failure with disk storage or memory causes a problem that has nothing to do with your code; but your

Thus, a block or subprogram can have only one OTHERS handler. But, according to the scope rules, enclosing blocks cannot reference exceptions declared in a sub-block. For example, perhaps a table you query will have columns added or deleted, or their types changed. see here A cursor must be closed before it can be reopened.

This chapter discusses the following topics: Overview of PL/SQL Error Handling Advantages of PL/SQL Exceptions Predefined PL/SQL Exceptions Defining Your Own PL/SQL Exceptions How PL/SQL Exceptions Are Raised How PL/SQL Exceptions Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block. In Example 11-6, you alert your PL/SQL block to a user-defined exception named out_of_stock. You must raise user-defined exceptions explicitly.

When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends. If you specify TRUE, PL/SQL puts error_code on top of the error stack. But when the handler completes, the block is terminated. Test your code with different combinations of bad input data to see what potential errors arise.