The behavior of NOCOPY. : NOCOPY « Function Procedure Packages « Oracle PL/SQL Tutorial






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>
SQL> show errors
No errors.
SQL>
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>
SQL>
SQL>
SQL>
SQL>
SQL>








27.18.NOCOPY
27.18.1.You can pass variables by reference, even in PL/SQL
27.18.2.The hint NOCOPY is applicable only to OUT and IN OUT types of variables
27.18.3.Performance improvement of NOCOPY
27.18.4.IN OUT NOCOPY
27.18.5.The behavior of NOCOPY.