The NOCOPY Parameter Mode Hint

Starting with Version 8.1, PL/SQL offers an option for definitions of parameters: the NOCOPY clause. NOCOPY is a hint to the compiler about how you would like the PL/SQL engine to work with the data structure being passed in as an OUT or IN OUT parameter. To understand NOCOPY and its potential impact, it will help to review how PL/SQL handles parameters. There are two ways of passing parameter values: by reference and by value.

By reference

When an actual parameter is passed by reference, it means that a pointer to the actual parameter is passed to the corresponding formal parameter. Both the actual and formal parameters then reference, or point to, the same location in memory that holds the value of the parameter.

By value

When an actual parameter is passed by value, the value of the actual parameter is copied into the corresponding formal parameter. If the program then terminates without an exception, the formal parameter value is copied back to the actual parameter. If an error occurs, the changed values are not copied back to the actual parameter.

Parameter passing in PL/SQL without the use of NOCOPY follows these rules:

Parameter mode	Passed by value or reference? (default behavior)
IN	By reference
OUT	By value
IN OUT	By value

We can infer from these definitions and rules that when a large data structure (such as a collection, a record, or an instance of an object type) is passed as an OUT or IN OUT parameter, that structure will be passed by value, and your application could experience performance and memory degradation as a result of all this copying. The NOCOPY hint is a way for you to attempt to avoid this. The syntax of this feature is as follows:

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

You can specify NOCOPY only in conjunction with the OUT or IN OUT mode. Here is a parameter list that uses the NOCOPY hint for both of its IN OUT arguments:

PROCEDURE analyze_results ( date_in IN DATE, values IN OUT NOCOPY numbers_varray, validity_flags IN OUT NOCOPY validity_rectype );

There are two things you should keep in mind about NOCOPY:

· The corresponding actual parameter for an OUT parameter under the NOCOPY hint is set to NULL whenever the subprogram containing the OUT parameter is called.

· NOCOPY is a hint, not a command. This means that the compiler might silently decide that it cannot fulfill your request for a NOCOPY parameter treatment. The next section lists the restrictions on NOCOPY that might cause this to happen.