NOCOPY Parameter Hint in PL/SQL

Before we get to understanding the NOCOPY parameter hint in PL/SQL, it would help to first look at PL/SQL parameter modes and the ways in which they pass values in and out of a subprogram.

PL/SQL subprograms have three parameter modes:

1. IN parameter mode is used to send values into the subprogram from the calling program. An IN parameter can only be read, its value cannot be modified inside the subprogram.
2. OUT parameter mode is used to return values from the subprogram to the calling program.
3. IN OUT parameter mode is a hybrid of IN and OUT. It is used to send values into the subprogram, and its value can be modified within the subprogram. On exit, the subprogram returns the updated value of the IN OUT parameter to the calling program.

A major difference between IN, OUT and IN OUT parameter modes is the way in which they pass a parameter to a subprogram: by reference or by value.

Parameter passing methods: by Reference vs. by Value

Pass by reference: The compiler sends to the subprogram a pointer to the actual parameter of the calling program. Any change to the parameter value inside the subprogram reflects in the calling program, since the parameter inside and outside the subprogram refer to the same memory location.

Pass by value: The compiler sends to the subprogram a copy of the actual parameter value from the calling program. A parameter passed by value can be modified independently of its value in the calling program. If the subprogram completes successfully, the parameter value is copied back to the calling program. If an exception occurs, the parameter value is not copied back to the calling program.

This thread explains the concept with a nice analogy — passing by reference is like sharing the URL of this page, while passing by value is like handing out a printout of this page. 

By default,
IN parameters are passed by reference.
OUT and IN OUT parameters are passed by value.

Passing by value – and the problem of performance

When pass-by-value parameters (i.e. OUT and IN OUT parameters) are large data structures like collections, records, and object types, the copying of data from subprogram to calling program slows down execution and uses up memory. For smaller data structures, the difference in performance between passing by value vs passing by reference would be insignificant, but the overhead can be large for large data structures.

That’s where the NOCOPY parameter hint comes to the rescue.

What does NOCOPY do?

NOCOPY is a hint to the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value. The use of NOCOPY saves on the processing and memory overhead of copying data from subprogram to calling program.

Why is NOCOPY called a hint, not a command?

NOCOPY is called a hint because unlike with a command, it is not mandatory for the PL/SQL compiler to honor the hint all the time.

The compiler can ignore the PL/SQL NOCOPY hint in such scenarios:

• The parameter passed is an element of an associative array in the calling program.[ This restriction does not apply if the parameter is an entire associative array.]
• The parameter passed has a NOT NULL constraint or a scale specified. e.g. a variable defined as NUMBER(5) in the calling program is used to call process_number(p_num IN OUT NOCOPY NUMBER).
• The parameters in the calling program and subprogram are records, one or both records are declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.
• The parameters in the calling program and subprogram are records, the parameter in the calling program is the index of a cursor FOR loop, and constraints on corresponding fields in the records differ.
• An implicit datatype conversion happens in passing the data from the calling program to the subprogram.

Though the PL/SQL compiler opts for the default pass-by-value in spite of the NOCOPY hint in the cases above, no warning message is generated.

Caution! NOCOPY and program execution errors

The downside of using NOCOPY is that you cannot trust the value of NOCOPY parameters if the subprogram results in error.

With normal OUT and IN OUT parameters [pass-by-value], the return values are modified only when the subprogram exits successfully. If the subprogram exits with error, the parameter values do not modified.

But with OUT and IN OUT parameters using NOCOPY, parameter values may get modified even if the subprogram exits with error, since any changes made to the parameters inside the subprogram are immediately copied to actual parameters of the calling program. To complicate things further, because NOCOPY is only a hint, one cannot know for certain if NOCOPY passed parameters to the subprogram by value or by reference. If the subprogram exits with error, the values of the parameters become unpredictable.

So when should you use NOCOPY?

Use NOCOPY when both of these conditions are true:

• the OUT or IN OUT parameters of a subprogram use large data structures causing performance issues in parameter passing
• the calling program can ignore the parameter values returned by the subprogram if the subprogram exits with error