What is the use of nocopy parameter in oracle procedure

Hi,
What is nocopy parameter in oracle procedure. what is the use of it. In which situation,we can use the nocopy parameter.
Thanks,
Saravanan.P

Questions by ily_saravanan   answers by ily_saravanan

Showing Answers 1 - 12 of 12 Answers

Jegadeesan

  • Mar 26th, 2007
 

      NOCOPY is a IN OUT COPY Which is used avoid the overhad of copying IN OUT Parameter Values.  If IN OUT parameter returns huge records we can specify the NOCOPY to copy the values.

      The PLSQL Engine first makes a copy of the record and then during program executing makes a changes to that copy.

Regards
Jegadeesan 

  Was this answer useful?  Yes

tharik

  • Mar 27th, 2007
 

NOCOPY will instruct Oracle to pass the argument as fast as possible. This can significantly enhance performance when passing a large value.

  Was this answer useful?  Yes

vmahawar

  • Apr 18th, 2007
 

NOCOPY Parameter in Oracle Procedure is used to pass arguments to the any procedure and function as Reference to improve the performance of the code.
This is done in case the arguments like records, pl/sql tables are passed to the called procedures.

  Was this answer useful?  Yes

sippsin

  • Jul 22nd, 2008
 

Ussually IN paramter is pass by value and OUT/IN OUT are pass by refererence....

If one wants to send the IN paramter too as pass by reference he could add NOCOPY
parameter....

advantage is always less memory usage

disadvantage is when there is a change happened to the Reffered value and a rollback occurs in the procedure the change of value would be retained.... it could not be rolled back....

so I beleive that it is always safe to work without the NOCOPY parameter.....

ratna82

  • Oct 10th, 2008
 

When a parameter is passed as an IN variable, it is passed by reference.  Since it will not change, PL/SQL uses the passed variable in the procedure/function.  When variables are passed in OUT or INOUT mode, a new variable is define, and the value is copied to the passed variable when the procedure ends.  If the variable is a large structure such as a PL/SQL table or an array, the application could see a performance degradation cause by copying this structure. 

The NOCOPY clause tells to PL/SQL engine to pass the variable by reference, thus avoiding the cost of copying the variable at the end of the procedure.  The PL/SQL engine has requirements that must be met before passing the variable by reference and if those requirements are not met, the NOCOPY clause will simply be ignored by the PL/SQL engine.

vanishavadlya

  • Oct 29th, 2009
 

Three types of parameter modes

1) IN parameter mode- This mode is used to pass values to the calling module when invoked. The value of IN parameter can't be changed in the module.


2) OUT parameter mode -This mode is used to return a value to the main block. The value of OUT parameter can change anywhere in the program.


3) IN OUT parameter mode-This mode is used to pass values to the calling module and return a value to the main block. The value of IN OUT parameter can change anywhere in the program.


In Call By value, the copy of actual parameter is passed to the formal parameter, So any changes to the formal parameter doesn't affect the actual parameter.


In Call By reference, the address of actual parameter is passed to the formal parameter, so any changes to the formal parameter will change the actual parameter also, because both of them are pointing to the same memory location.  Here no copying is required.


The IN parameter is passes by reference, so we can't change the value of IN parameter inside the module, It acts as a constant, But the OUT and IN OUT parameters are passed by value, we can change the values of OUT & IN OUT parameters if you use the hint NOCOPY with OUT Parameter and IN OUT Parameter then ::: call by reference


debjit20

  • Apr 8th, 2010
 

When a parameter is passed as an IN variable, it is passed by reference.  Since it will not change, PL/SQL uses the passed variable in the procedure/function.  When variables are passed in OUT or INOUT mode, a new variable is define, and the value is copied to the passed variable when the procedure ends.  If the variable is a large structure such as a PL/SQL table or an array, the application could see a performance degradation cause by copying this structure.   The NOCOPY clause tells to PL/SQL engine to pass the variable by reference, thus avoiding the cost of copying the variable at the end of the procedure.  The PL/SQL engine has requirements that must be met before passing the variable by reference and if those requirements are not met, the NOCOPY clause will simply be ignored by the PL/SQL engine.

  Was this answer useful?  Yes

ptmich

  • Feb 28th, 2012
 

sipsin: Actually the reverse is true. Usually the IN parameter is passed by reference and the OUT/IN OUT parameters are passed by value.

  Was this answer useful?  Yes

Saket

  • Apr 3rd, 2012
 

Pass by reference: "IN" Case
Pass by value : "Out" or "IN OUT" Case which have the overhead of copying the value to new procedure parameter.
In order to make to Pass by reference we will use NOCOPY
Pass by reference: "OUT NOCOPY" or "IN OUT NOCOPY" which deals which the actual reference value.

  Was this answer useful?  Yes

Himansu

  • Nov 6th, 2012
 

In procedure,Function there are three types of parameter is there. eg-IN, OUT, INOUT.
IN parameter is call by reference and OUT & INOUT are call by value. Always call by reference is faster than call by value. We use NOCOPY to convert call by value to call by reference.

Actually it is more useful when we are passing a large record or a large table.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions