NOCOPY Parameter Mode (Hint)

There are two ways to pass parameter values:

1.By reference : Pointer of the actual parameter is passed to the formal parameter.

2.By value : value of the actual parameter is passed to the formal parameter & if program
                     terminates without an exception,the formal value is copied back to actual parameter.


                    IN : BY reference

OUT / IN OUT : By value ( If the called block fails, the values are not copied back to actual parameter)


If a large data structure is passed as an OUT / IN OUT parameter, that structure will be passed by value, and our application could experience performance degradation (copying).

To avoid this, we use NOCOPY.


parameter_name [ IN | IN OUT | OUT | IN OUT NOCOPY | OUT NOCOPY ] parameter_datatype


Depending on your application, NOCOPY can improve the performance of programs with IN OUT or OUT parameters. As we might expect, these potential gains come with a tradeoff: if a program terminates with an unhandled exception, we cannot trust the values in a NOCOPY actual parameter.


NOCOPY is a hint, not a command. This means that the compiler might silently decide that it can't fulfill your request for a NOCOPY parameter treatment.

Example:

Create or Replace Procedure test_out_par
(my_out_var out NOCOPY varchar2,
 my_in_var in varchar2)
Is
  my_exception exception;
Begin
  my_out_var:=my_in_var;
  raise my_exception;
End;
/


Declare
  my_out_var_kk varchar2(200);
  my_in_var_kk  varchar2(200);
Begin
  my_in_var_kk :='kiran';
  my_out_var_kk :='kodavati';
  dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
  dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
  test_out_par (my_out_var_kk,my_in_var_kk);
Exception
  when others then
    dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
    dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
End;
/

Note: Even we got the exception the variable value got changed (MY_OUT_VAR_KK) because of NOCOPY.