Oracle diary Jun27 2011

PL/SQL / Named Program Units / Parameters:

Procedures, functions, and cursors may have a parameter list. The synax is

parameter_name [mode ] [NOCOPY] datatype [ { := | DEFAULT } value ]

 

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

 

OUT parameters are not the same as IN OUT parameters. When running the called program, the runtime engine ignores (sets to NULL) any argument value you supply for an OUT parameter; it preserves the value provided for an IN OUT. If an exception is raised during execution of a procedure or function, assignments made to OUT or IN OUT parameters get rolled back unless the parameter includes the NOCOPY option.

 

The NOCOPY hint alleviates this problem because you can use it to instruct the runtime engine to try to pass OUT or IN OUT parameters by reference instead of by value.

 

A NOCOPY hint can offer a small performance boost, but you must be careful and know how it affects program behavior, in particular exception handling.(Couldnot “undo” or “rollback”  modifications to NOCOPY parameters)