Nocopy

In today's plsqlchallenge I learned another new thing. It is the NOCOPY option for the parameters in a procedure or a function. I was under the impression that when we pass a variable with the nocopy option the variables changes in the procedures will not be passed over. But that doesn't seem to be correct. When we pass a parameter with the NOCOPY option, no seperate copy of the variable is made in the memory and the passed variable is used to modify. This makes more sense while using collection variables. If for example we pass a VARRAY as in out parameter without the NOCOPY option then the variable is copied to a temporary variable and then the changes are made. In the case of NOCOPY the changes are made to the original variable itself. In case an exception occurs then in the case of NOCOPY the original variable would have changed but in the case where NOCOPY is not specified the orginal value remains intact. Below is the sample code from todays sample quiz.



create or replace package plch_pkg
is
type number_nt is table of number;

procedure pass_nums1 (nums in out number_nt);

procedure pass_nums2 (nums in out nocopy number_nt);

procedure show_nums (nums in number_nt);

end;
/

create or replace package body plch_pkg
is
procedure pass_nums1(nums in out number_nt) is
begin
for indx in nums.first..nums.last
loop
nums(indx):=nums(indx)*2;
raise value_error;
end loop;
end pass_nums1;

procedure pass_nums2(nums in out nocopy number_nt) is
begin
for indx in nums.first..nums.last
loop
nums(indx):=nums(indx)*2;
raise value_error;
end loop;
end pass_nums2;

procedure show_nums(nums in number_nt) is
l_return pls_integer :=0;
begin
for indx in nums.first..nums.last
loop
l_return := l_return+nums(indx);
end loop;
dbms_output.put_line(l_return);
end show_nums;
end plch_pkg;
/

show err


declare
nums1 plch_pkg.number_nt :=plch_pkg.number_nt(1,2,3);
nums2 plch_pkg.number_nt :=plch_pkg.number_nt(1,2,3);
begin
begin
plch_pkg.pass_nums1(nums1);
exception when others then
plch_pkg.show_nums(nums1);
end;
begin
plch_pkg.pass_nums2(nums2);
exception when others then
plch_pkg.show_nums(nums2);
end;
end;
/