SQL>
SQL> CREATE OR REPLACE PROCEDURE NoCopyTest(p_In IN NUMBER,p_Out OUT NOCOPY VARCHAR2,p_InOut IN OUT NOCOPY CHAR) IS
2 BEGIN
3 NULL;
4 END NoCopyTest;
5 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- A modified version of RaiseError, with the out parameter specified as NOCOPY.
SQL> CREATE OR REPLACE PROCEDURE RaiseErrorNoCopy(p_Raise IN BOOLEAN,pA OUT NOCOPY NUMBER) AS
2 BEGIN
3 pA := 5;
4 IF p_Raise THEN
5 RAISE DUP_VAL_ON_INDEX;
6 ELSE
7 RETURN;
8 END IF;
9 END RaiseErrorNoCopy;
10 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- When we call RaiseErrorNoCopy the exception semantics are changed due to NOCOPY.
SQL> DECLARE
2 v_Num NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Value before first call: ' || v_Num);
5 RaiseErrorNoCopy(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 RaiseErrorNoCopy(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: 5
Value before second call: 2
Value after unsuccessful call: 5
PL/SQL procedure successfully completed.
SQL>