using exception handling in pl/sql – the new oracle dba reference





The new oracle dba can fine here an example of using exception handling in pl/sql blocks.

You can still handle an exception for a statement, then continue with the next

statement. Place the statement in its own sub-block with its own exception handlers. If

an error occurs in the sub-block, a local handler can catch the exception. When the

sub-block ends, the enclosing block continues to execute at the point where the

sub-block ends, as shown in Example 10–12.

Example 10–12 Continuing After an Exception

DECLARE

sal_calc NUMBER(8,2);

BEGIN

INSERT INTO employees_temp VALUES (303, 2500, 0);

BEGIN — sub-block begins

SELECT salary / commission_pct INTO sal_calc FROM employees_temp

WHERE employee_id = 301;

EXCEPTION

WHEN ZERO_DIVIDE THEN

sal_calc := 2500;

END; — sub-block ends

INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);

EXCEPTION

WHEN ZERO_DIVIDE THEN

NULL;

END;

/

In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the

local handler catches it and sets sal_calc to 2500. Execution of the handler is

complete, so the sub-block terminates, and execution continues with the INSERT

statement.

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 the %BULK_EXCEPTIONS Attribute" on

page 11-14.

Author: admin