Behavior of OUT variables and raised exceptions
SQL> CREATE OR REPLACE PROCEDURE RaiseError (p_Raise IN BOOLEAN,pA OUT NUMBER) AS
2 BEGIN
3 pA := 7;
4
5 IF p_Raise THEN
6 RAISE DUP_VAL_ON_INDEX;
7 ELSE
8 RETURN;
9 END IF;
10 END RaiseError;
11 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> DECLARE
2 v_Num NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Value before first call: ' || v_Num);
5 RaiseError(FALSE, v_Num);
6 DBMS_OUTPUT.PUT_LINE('Value after successful call: ' || v_Num);
7 DBMS_OUTPUT.PUT_LINE('');
8
9 v_Num := 2;
10 DBMS_OUTPUT.PUT_LINE('Value before second call: ' || v_Num);
11 RaiseError(TRUE, v_Num);
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE('Value after unsuccessful call: ' || v_Num);
15 END;
16 /
Value before first call: 1
Value after successful call: 7
Value before second call: 2
Value after unsuccessful call: 2
PL/SQL procedure successfully completed.
SQL>