Oracle PL/SQL in out nocopy

Suppose a subprogram declaresINParameter,OUTParameter, andIN OUTParameter. When you call the subprogram,INParameter is passed by reference. That is, a pointer toINActual parameter is passed to the corresponding formal parameter. So, both parameters reference the same memory location, which holds the value of the actual parameter.

By default,OUTAndIN OUTParameters are passed by value. That is, the value ofIN OUTActual parameter is copied into the corresponding formal parameter. Then,If the subprogram Exits normally, the values assigned toOUTAndIN OUTFormal parameters are copied into the corresponding actual parameters.

 

When we declare a parameter of the in type, passing the parameter is a pointer to this parameter;

When we declare a parameter of the out or in out type, passing the parameter will be a copy of the parameter;

The value assigned to an out or in out parameter is returned only when the program ends normally (unless nocopy is used ).

Nocopy can be used to optimize performance when passing parameters with large data volumes (such as collections, records, and instances of object types.

When the parameter is of the out or in out type: No nocopy = pass by value (byval); plus nocopy = pass by reference (byref ).

 

Test code:

 

Declare <br/> L_1 number: = 10; <br/> L_2 number: = 20; <br/> L_3 number: = 30; <br/> procedure test_out <br/> (<br/> P1 in number <br/>, X1 in out number <br/>, x2 in out nocopy number <br/>) is <br/> begin <br/> X1: = p1; <br/> dbms_output.put_line ('inside test_out, x1 = '| X1); <br/> X2: = p1; <br/> dbms_output.put_line ('inside test_out, X2 =' | X2 ); <br/> raise_application_error (-20001, 'test nocopy'); <br/> end; <br/> begin <br/> dbms_output.put_line ('before, l_1 = '| L_1 |', L_2 = '| L_2 | <br/>', L_3 = '| L_3 ); <br/> begin <br/> -- The out parameter has no value at all until the program terminates successfully, <br/> -- unless you have requested use of the nocopy hint <br/> test_out (L_1, L_2, L_3 ); <br/> exception <br/> when others then <br/> dbms_output.put_line ('sqlcode => '| sqlcode | ', sqlerrm => '| <br/> sqlerrm); <br/> end; <br/> dbms_output.put_line ('after, l_1 =' | L_1 | ', l_2 = '| L_2 |', L_3 = '| L_3); <br/> end; <br/>

 

Output:

 

Before, l_1 = 10, L_2 = 20, L_3 = 30 <br/> inside test_out, X1 = 10 <br/> inside test_out, x2 = 10 <br/> sqlcode =>-20001, sqlerrm => ORA-20001: Test nocopy <br/> after, l_1 = 10, L_2 = 20, l_3 = 10 <br/>

 

Ref:

Http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96624/08_subs.htm#12813