Abhishek Ghosh

• Dec 12th, 2006

 

NOCOPY Parameter Option
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) ISBEGIN   ...END;

Arun

• Dec 12th, 2006

 

NOCOPY is a hint given to the compiler, indicating that the parameter is passed as a reference and hence actual value should not be copied in to the block and vice versa. The processing will be done accessing data from the original variable. (Which other wise, oracle copies the data from the parameter variable into the block and then copies it back to the variable after processing. This would put extra burdon on the server if the parameters are of large collections/sizes)

Arun

• Dec 12th, 2006

 

I am sorry I didn't notice the second part of your question. This is used with the declaration of OUT parameter.eq. PROCEDURE my_proc (param1 IN varchar2, param2 OUT NOCOPY varchar2, param3 IN OUT NOCOPY varchar2)

iasritesh

 Profile Answers by iasritesh Questions by iasritesh

• Jan 14th, 2007

 

For better understanding of NOCOPY parameter, I will suggest u to run the following code and see the result.

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;

Thanks & Regards,

Ritesh Kumar.

raghav_sy

 Profile Answers by raghav_sy 

• Jan 15th, 2007

 

hi one more thing i wan to add abt NOCOPY, it is that its a hint to compiler, so its compiler wish whether to follow this thing or not, so before using this just go through the conditions when this thing will not be taken into account. SO ITS NOT MANDATORY FOR COMPILER TO USE NOCOPY).

like during RPC(remote procedure call), NOCOPY will be not be used.

take care.

PraGop

 Profile Answers by PraGop 

• Jul 21st, 2008

 

Can u please give more explanation on this NOCOPY parameter