What is NOCOPY hint?

Prior to Oracle 8i there were three types of parameter-passing options to procedures and functions:
    * IN: parameters are passed by reference
    * OUT: parameters are implemented as copy-out
    * IN OUT: parameters are implemented as copy-in/copy-out

The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised so that changes could be rolled back. Because a copy of the parameter set was made rollback could be done. However this method imposed significant CPU and memory overhead when the parameters were large data collections for example PL/SQL Table or VARRAY types.

With the new NOCOPY option OUT and IN OUT parameters are passed by reference which avoids copy overhead. However parameter set copy is not created and in case of an exception rollback cannot be performed and the original values of parameters cannot be restored.

Here is an example of using the NOCOPY parameter option:

TYPE Note IS RECORD( Title VARCHAR2(15), Created_By VARCHAR2(20), Created_When DATE, Memo VARCHAR2(2000));
TYPE Notebook IS VARRAY(2000) OF Note;
CREATE OR REPLACE PROCEDURE Update_Notes(Customer_Notes IN OUT NOCOPY Notebook) IS BEGIN ...END;