Oracle Pl/SQL - NOCOPY Parameter

The PL/SQL has two different methods for passing parameter values between stored procedures and functions,

1.       Pass by value
            Actual value is being copied to another memory location for the calling procedure/function to use
            i.e it copies the actual value of the parameter into the formal parameter. So, both called and calling procedures has got 2 different memory locations to store the value

2.      Pass by reference

Only the memory location (address) is passed so that the data is not copied. The calling procedure and the called procedure, both uses the same value stored in a single memory location. So the actual and the formal parameters refer to the same memory location that holds the value.

As we know, there are three different type of parameters in PL/SQL:

We specify any one of the below types, along with the parameters so classify them as:

IN                        - Parameters passed to the procedure/function
OUT                  - Parameters that store the values returned (explicitly) from the procedure/function
IN OUT              - Can pass values to the procedure and store the returned values

            And by default, OUT and IN OUT parameters are passed by value whereas the IN parameters are always passed by reference. IN parameters are designed in such a way to reduce memory consumption and at the same time, the values won t get overwritten since IN doesn t allow give  write  access to the memory location.

            And for OUT and IN OUT, when the values are modified by either the called procedure/function or the calling procedure/function, only their local copies get affected and not the original ones.

Explanation of NOCOPY parameter:

            When you use a Collection (Records, VARRAY etc having records of data) as a Return type (OUT or IN OUT parameter) as we know, it will be passed by value only. That means the entire data will be copied to the formal parameter location, which leads to lot of CPU and memory consumption.

Here comes the usage of NOCOPY parameter. Provide NOCOPY after the OUT/IN OUT parameter, which instructs the PL/SQL Engine to avoid using the pass by value method. So, the Engine uses pass by reference instead. This helps reducing a lot of CPU usage as well as memory consumption.

Eg:  Just an example how to put the NOCOPY parameter.
 
PROCEDURE PRC_NOCOPY_EXAMPLE
(
            P_EMPNO                   IN                                            NUMBER,
            P_ENAME                   IN                                            VARCHAR2(50),
            P_SAL                         OUT    NOCOPY                    RECORD_SAL                    --PL/SQL Record
);
REC_SAL RECORD_SAL;

Now if you call the  Procedure PRC_NOCOPY_EXAMPLE, the parameter P_SAL would only use Pass by reference method.

NOTE:

Disadvantage:

            However, you need to be extremely careful while using this method in coding for high priority data transactions. When you pass parameters to a procedure by reference, any change  that happens on the passed Parameters gets updated in the same memory location as the Actual parameter. So when an exception occurs in a procedure/function, these changes are  not Rolled back . To be precise, the PL/SQL engine cannot rollback these parameter changes. So, if the actual values get changed, then it may result in incorrect results.

Thus, to conclude, it is up to a Developer to take a trade-off between using or not using the NOCOPY parameter. NOCOPY could provide better performance by reducing Memory and CPU cost, but at the same time, could result in incorrect results too.