What is No Copy in Oracle

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_SALary    OUT NOCOPY salary_record --PL/SQL Record type
);
sal_rec salary_record;

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


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.

Here are some places where this is not possible:

When the call is a remote procedure call
When the actual parameter being passed is an expression
When there is an implicit conversion involved



Example2: For better understanding of NOCOPY.
--------

set serveroutput on;
DECLARE
n NUMBER := 10;
PROCEDURE do_something (
n1 IN NUMBER,
n2 IN OUT NUMBER ,
n3 IN OUT NOCOPY NUMBER) IS
BEGIN
n2 := 20;
DBMS_OUTPUT.PUT_LINE(n1); -- prints 10
n3 := 30;
DBMS_OUTPUT.PUT_LINE(n1); -- prints 30
END;
BEGIN
do_something(n, n, n);
DBMS_OUTPUT.PUT_LINE(n); -- prints 20
END;


Ur's
AmarAlam