GeekInterview.com
Series: Subject: Topic:
Question: 125 of 290

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
Asked by: ily_saravanan | Member Since Oct-2006 | Asked on: Mar 19th, 2007

View all questions by ily_saravanan   View all answers by ily_saravanan

Showing Answers 1 - 11 of 11 Answers

           no copy parameter is an argument in a procedure
                              it says to oracle  to pass the variable value back to the user as quickly as possible.

  
Login to rate this answer.
Jegadeesan

Answered On : 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 

  
Login to rate this answer.
tharik

Answered On : Mar 27th, 2007

View all answers by tharik

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

  
Login to rate this answer.
vmahawar

Answered On : 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.

  
Login to rate this answer.
sippsin

Answered On : Jul 22nd, 2008

View all answers by sippsin

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.....

Yes  1 User has rated as useful.
  
Login to rate this answer.
ratna82

Answered On : Oct 10th, 2008

View all answers by ratna82

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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
amianan

Answered On : Nov 10th, 2008

View all answers by amianan

TO PASS OUT AND INOUT Parameters by refrenece instead of value.

  
Login to rate this answer.
vanishavadlya

Answered On : Oct 29th, 2009

View all answers by vanishavadlya

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


Yes  2 Users have rated as useful.
  
Login to rate this answer.
debjit20

Answered On : Apr 8th, 2010

View all answers by debjit20

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.

  
Login to rate this answer.
ptmich

Answered On : Feb 28th, 2012

View all answers by ptmich

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

  
Login to rate this answer.
Saket

Answered On : 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.

  
Login to rate this answer.
Himansu

Answered On : 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.

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.