Unhandled exceptions and OUT variables : Parameter OUT « Stored Procedure Function « Oracle PL / SQL

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

Unhandled exceptions and OUT variables

    
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
  2    p_Raise IN BOOLEAN,
  3    p_ParameterA OUT NUMBER) AS
  4  BEGIN
  5    p_ParameterA := 7;
  6
  7    IF p_Raise THEN
  8      RAISE DUP_VAL_ON_INDEX;
  9    ELSE
 10      RETURN;
 11    END IF;
 12  END RaiseError;
 13  /

Procedure created.

SQL>
SQL> set serveroutput on
SQL> DECLARE
  2    v_TempVar NUMBER := 1;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('Initial value: ' || v_TempVar);
  5    RaiseError(FALSE, v_TempVar);
  6    DBMS_OUTPUT.PUT_LINE('Value after successful call: ' || v_TempVar);
  7
  8    v_TempVar := 2;
  9    DBMS_OUTPUT.PUT_LINE('Value before 2nd call: ' || v_TempVar);
 10    RaiseError(TRUE, v_TempVar);
 11  EXCEPTION
 12    WHEN OTHERS THEN
 13      DBMS_OUTPUT.PUT_LINE('Value after unsuccessful call: ' || v_TempVar);
 14  END;
 15  /
Initial value: 1
Value after successful call: 7
Value before 2nd call: 2
Value after unsuccessful call: 2

PL/SQL procedure successfully completed.

SQL>
SQL>

   
    
    
  

Related examples in the same category