Behavior of OUT variables and raised exceptions : Parameter OUT « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Parameter OUT

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>

   
    
    
  

Related examples in the same category