Skip to content

NOCOPY Parameter Hint

In Oracle PL/SQL runtime engine, there are two different ways to pass values for OUT and IN OUT parameters.

Pass By Value:

This is the default behavior of passing OUT and IN OUT parameters that "copy" the actual value of the parameter into some temporary buffer storage (formal parameter) during the execution of the PL/SQL PROCEURE or FUNCTION. After the successful execution of such PL/SQL subprogram, the updated value in the temporary buffer storage is copied back to the actual parameter variable. It's noted that in the event of EXCEPTIONs are raised inside the PL/SQL PROCEURE or FUNCTION then, there is high chance of expected copying back values doesn't happen.

Pass By Reference:

By using NOCOPY parameter hint, PL/SQL Runtime engine utilize the same memory location as a Reference to hold both actual and formal parameters. Therefore, it's not needed any temporary buffer to store the values and COPY these values forward and backward between the parameters. Instead, any changes to the parameter values are directly written to the actual parameter location.

When to use NOCOPY:

The NOCOPY parameter hint would be a good option specially when passing large PL/SQL data structures like BLOB, CLOB, etc as OUT and IN OUT parameters in a PROCEURE or FUNCTION that causes the performance issues in parameter passing.

NOTE: The downside of using NOCOPY hint is that the final result of the NOCOPY parameter cannot be trusted incase Oracle unhandled ERRORs are raised inside the logic of PROCEDURE or FUNCTION without implementing proper EXCEPTION handling mechanism.

Examples:

PROCEDURE Write_Lob___ (
   lob_     IN OUT NOCOPY CLOB,
   buffer_  IN OUT NOCOPY VARCHAR2,
   text_    IN VARCHAR2 )
IS
BEGIN
END Write_Lob___;
PROCEDURE Validate_Codes___ (
   newrec_ IN OUT NOCOPY ROUT_OPERATION_TEMPLATE_TAB%ROWTYPE )
IS
BEGIN  
END Validate_Codes___;