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.
It is recommended to use the complex data type like LOBS,XML datatypes, collections etc..
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.