Categories: SQL Complex Queries

What is NOCOPY hint with real examples? | NOCOPY hint examples

In my previous article I have given the examples of what is mean by hint in SQL and how those are used in tuning the SQL queries. We can pass IN or IN OUT parameters by value. But if you pass the parameters by value the process of execution of PLSQL code will be slowed down. We have checked so many oracle hints in previous articles but in this article I would like to throw light on NOCOPY hint with examples.

What you will see in this article :

What is NOCOPY hint with examples?

How to use NOCOPY hint in PLSQL examples.

What is NOCOPY hint with examples?

The NOCOPY hint allows the PLSQL compiler to pass OUT and IN OUT parameters by reference rather than by value. The reason behind passing the parameters by reference is to improve the performance of PL SQL code. It reduces the overhead of passing parameters.

NOCOPY hint

It is recommended to use the complex data type like LOBS,XML datatypes, collections etc..

IN OUT -Pass by reference

We require to use the NOCOPY hint which will fasten up the process of PLSQL program and these kind of hints are useful for performance improvements at application side.

What is NOCOPY hint examples?

If the subprogram exits with an exception that is not handled.

1.You can not rely on the values of actual parameters which is passed to NOCOPY parameters.

2.Any incomplete modifications are not rolled back.

3.The Remote procedure call (RPC) enables you to pass parameters only by value.

When PLSQL compiler will ignore the NOCOPY?

The NOCOPY hint is not effected in :

1.If actual parameter is an index by value.

2.If actual parameter is constrained.

3.An actual parameter and formal parameter are records where one or both records were declared by %ROWTYPE or %TYPE,and constraints on corresponding fields in the records differs.

4.It requires an implicit data conversion.

5.If subprogram like procedure, functions are involved in external or remote procedure calls.

Real Life Industry examples :

/*
Example for OUT and INOUT parameter passing by reference
1-Pass By Value ( default)
2-Pass By Reference using no copy

*/


What is mean Pass By Value?

The default action is to create a temporary buffer (formal parameter),copy the data from the parameter variable (actual parameter) to that buffer and work on the temporary buffer during the lifetime of the procedure.
On successful completion of the procedure, the contents of the temporary buffer are copied back into the parameter variable.
In the event of an exception occurring, the copy back operation does not happen.

create or replace procedure P_pass_by_value1
( P_id IN out number)
is
begin
P_id:=P_id*10;
if P_id>100 then
raise VALUE_ERROR;
end if;
end;

declare
v number:=20;
begin
pass_by_value1(V);
dbms_output.put_line(‘NO ISSUE ‘||v);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line(‘EXCEPTION ‘||v);

end;

Pass by reference :

create or replace procedure P_pass_by_ref
( P_id IN out nocopy number)
is
begin
P_id:=P_id*10;
if P_id>100 then
raise VALUE_ERROR;
end if;
end;

declare
v number:=20;
begin
pass_by_ref(V);
dbms_output.put_line(‘NO ISSUE ‘||v);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line(‘EXCEPTION ‘||v);

end;

The procedure with pass by reference is faster than the pass by value procedure. I hope you get clear idea about this. If you like this article or if you have any issues with the same kindly comment in comments section.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.

Share
Published by
Amit S

Recent Posts

What is Root Cause Analysis (RCA) With real examples

In my previous article I have given details about application support engineer day to day…

4 weeks ago

Application Support Engineer Day to day responsibilities

In my previous articles I have given the roles and responsibilities of L1,L2 and L3…

1 month ago

What is mean by SLA ( Service Level Agreement) with Examples?

In my previous articles i have given the hierarchy of production support in real company…

1 month ago

What is Production support Hierarchy in organization?

In this article i would like to provide information about production support organization structure or…

1 month ago

What are roles and responsibilities for L3 Support Engineer?

In my previous article I have given roles for L1 and L2 support engineer with…

1 month ago

What are roles and responsibilities of L2 Engineer?

I have started this new series of how to become application support engineer. This article…

1 month ago