Oracle PL/SQL Tutorial/Function Procedure Packages/NOCOPY

IN OUT NOCOPY

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE very_confusing (
  2     arg1   IN              VARCHAR2
  3   , arg2   IN OUT          VARCHAR2
  4   , arg3   IN OUT NOCOPY   VARCHAR2
  5  )
  6  IS
  7  BEGIN
  8     arg2 := "Second value";
  9     DBMS_OUTPUT.put_line ("arg2 assigned, arg1 = " || arg1);
 10     arg3 := "Third value";
 11     DBMS_OUTPUT.put_line ("arg3 assigned, arg1 = " || arg1);
 12  END;
 13  /
Procedure created.
SQL>
SQL> DECLARE
  2     str   VARCHAR2 (100) := "First value";
  3  BEGIN
  4     DBMS_OUTPUT.put_line ("str before = " || str);
  5     very_confusing (str, str, str);
  6     DBMS_OUTPUT.put_line ("str after = " || str);
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>

Performance improvement of NOCOPY

SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
  2  is
  3    type arr is varray(100000)of product%ROWTYPE;
  4    procedure p1(ip1 IN OUT arr);
  5    procedure p2(ip1 IN OUT NOCOPY arr);
  6    FUNCTION get_time RETURN NUMBER;
  7  END myPackage;
  8  /
Package created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
  2  is
  3    PROCEDURE p1(ip1 IN OUT arr)
  4    IS
  5    BEGIN
  6      NULL;
  7    END;
  8    PROCEDURE p2(ip1 IN OUT NOCOPY arr)
  9    IS
 10    BEGIN
 11      NULL;
 12    END;
 13    FUNCTION get_time RETURN NUMBER
 14    IS
 15    BEGIN
 16      RETURN (dbms_utility.get_time);
 17    EXCEPTION WHEN OTHERS THEN
 18      RAISE_APPLICATION_ERROR(-20010,SQLERRM);
 19    END get_time;
 20  END myPackage;
 21  /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> declare
  2    arr1 myPackage.arr :=myPackage.arr(null);
  3    cur_t1 number;
  4    cur_t2 number;
  5    cur_t3 number;
  6  begin
  7    select * into arr1(1)from product where product_id =1;
  8
  9
 10    arr1.extend(99999,1);
 11    cur_t1 :=myPackage.get_time;
 12    myPackage.p1(arr1);
 13    cur_t2 :=myPackage.get_time;
 14    myPackage.p2(arr1);
 15    cur_t3 :=myPackage.get_time;
 16
 17    dbms_output.put_line("Without NOCOPY "||to_char((cur_t2-cur_t1)/100));
 18    dbms_output.put_line("With NOCOPY "||to_char((cur_t3-cur_t2)/100));
 19  end;
 20  /
Without NOCOPY .17
With NOCOPY 0
PL/SQL procedure successfully completed.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL>

The behavior of NOCOPY.

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>

The hint NOCOPY is applicable only to OUT and IN OUT types of variables

SQL>
SQL> create or replace procedure p_validate(io_string IN OUT NOCOPY VARCHAR2)
  2  is
  3      v_invalid VARCHAR2(8):="!@#$%^&";
  4  begin
  5      io_string:=replace (io_string,v_invalid);
  6      if length(io_string)>4000
  7      then
  8          io_string:=substr(io_string,1,3997)||"...";
  9      end if;
 10  end;
 11  /
Procedure created.
SQL>

You can pass variables by reference, even in PL/SQL

SQL>
SQL> create or replace procedure p_validate(io_string IN OUT NOCOPY VARCHAR2)
  2  is
  3      v_invalid VARCHAR2(8):="!@#$%^&";
  4  begin
  5      io_string:=replace (io_string,v_invalid);
  6      if length(io_string)>4000
  7      then
  8          io_string:=substr(io_string,1,3997)||"...";
  9      end if;
 10  end;
 11  /
Procedure created.
SQL>