NOCOPY

rajesh@ORA10GR2> create or replace procedure p1(x out number,y out number)
  2  as
  3  begin
  4     x := 55;
  5     y := 55;
  6     raise no_data_found;
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:00.17
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     l_x number := 0;
  3     l_y number := 0;
  4  begin
  5     p1(l_x,l_y);
  6  exception
  7             when no_data_found then
  8                     dbms_output.put_line ('x = '||l_x);
  9                     dbms_output.put_line ('y = '||l_y);
 10  end;
 11  /
x = 0
y = 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
rajesh@ORA10GR2>


The values of OUT paramaters are assigned on the sucessfull completion of the subroutines. But that is not the case with NOCOPY they are like pointer variables. ( As soon as we modify them in sub-routines their values will be modified in calling routines, As you see below )

rajesh@ORA10GR2> create or replace procedure p2(x out NOCOPY number,y out NOCOPY number)
  2  as
  3  begin
  4     x := 55;
  5     y := 55;
  6     raise no_data_found;
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:00.18
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     l_x number := 0;
  3     l_y number := 0;
  4  begin
  5     p2(l_x,l_y);
  6  exception
  7             when no_data_found then
  8                     dbms_output.put_line ('x = '||l_x);
  9                     dbms_output.put_line ('y = '||l_y);
 10  end;
 11  /
x = 55
y = 55

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
rajesh@ORA10GR2>

When Sending bulk volume of data between subroutines NOCOPY hint doesn't share data's actually. It will copy values PASS BY REFERENCE Technique.

rajesh@ORA10GR2> create or replace procedure p3(x out dbms_sql.varchar2a)
  2  as
  3  begin
  4     for r in 1..200000
  5     loop
  6             x(r) := rpad('*',4000,'*');
  7     end loop;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.21
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     y dbms_sql.varchar2a;
  3  begin
  4     p3(y);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:50.86
rajesh@ORA10GR2>


Now you see the effect of NOCOPY here.


rajesh@ORA10GR2> create or replace procedure p4(x out NOCOPY dbms_sql.varchar2a)
  2  as
  3  begin
  4     for r in 1..200000
  5     loop
  6             x(r) := rpad('*',4000,'*');
  7     end loop;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.21
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     y dbms_sql.varchar2a;
  3  begin
  4     p4(y);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.13
rajesh@ORA10GR2>