The NOCOPY hint to Oracle is a bit misleading and not very intuitive.
NOCOPY can only be used with IN OUT parameters.
Any time you use an IN OUT parameter, regardless of whether you use NOCOPY, changes made inside of the procedure will be visible outside of the procedure when no exception is raised by the procedure.
The sole difference between IN OUT and IN OUT NOCOPY is what happens when an exception is raised by the procedure. With an IN OUT parameter, changes made to the collection are reversed when an exception is raised. With an IN OUT NOCOPY parameter, changes are not reversed.
Let's first examine an IN OUT parameter with no exceptions to prove that changes made inside of a collection are visible outside, even without NOCOPY:
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
v_numbers t_numbers;
PROCEDURE work(
v_numbers IN OUT t_numbers
)
IS
BEGIN
FOR i IN 1 .. v_numbers.COUNT LOOP
v_numbers(i) := v_numbers(i) ** 2;
END LOOP;
END work;
BEGIN
v_numbers := t_numbers(1,2,3,4,5);
work(v_numbers);
FOR i IN 1 .. v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
END;
/
This outputs the following, proving that changes made inside the procedure are visible outside of the procedure:
1
4
9
16
25
If we change our work procedure to use IN OUT NOCOPY:
v_numbers IN OUT NOCOPY t_numbers
It results in the exact same output.
So there is no effective difference between IN OUT and IN OUT NOCOPY in the case where the procedure does not raise an exception.
However, what happens if an exception is raised? Let's get rid of NOCOPY and raise an exception.
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
v_numbers t_numbers;
PROCEDURE work(
v_numbers IN OUT t_numbers
)
IS
BEGIN
FOR i IN 1 .. v_numbers.COUNT LOOP
v_numbers(i) := v_numbers(i) ** 2;
END LOOP;
RAISE_APPLICATION_ERROR(-20001, 'error');
END work;
BEGIN
v_numbers := t_numbers(1,2,3,4,5);
work(v_numbers);
FOR i IN 1 .. v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
FOR i IN 1 .. v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
END;
/
This outputs the following:
1
2
3
4
5
So, when using IN OUT instead of IN OUT NOCOPY, no changes where made to the collection when the procedure raised an exception.
However, if we change it to NOCOPY:
v_numbers IN OUT NOCOPY t_numbers
it will output the following:
1
4
9
16
25
This shows that the sole difference between IN OUT and IN OUT NOCOPY is that the former will reverse changes when an exception is raised while the latter does not.
So what is NOCOPY doing?
It's not very intuitive.
Without NOCOPY, Oracle will pass in a reference and that reference will be used for all read operations that take place on the collection. The exact same thing occurs when you use IN instead of IN OUT. There is nothing special about this when it comes to read operations.
However, when a write operation takes place on an IN OUT parameter that does not have NOCOPY, Oracle will make a temporary copy, and all write operations will operate on the copy of this collection. If the procedure finishes without raising an exception, Oracle will then copy it back a second time into the parameter, and the changes will be visible.
If we instead use an IN OUT NOCOPY, no copying takes place.
This explains why raising an exception with NOCOPY doesn't reverse the changes.
At least for the code that I write, I almost always use IN OUT NOCOPY instead of IN OUT with collections. The only case in which I would prefer to use IN OUT is if I wanted to be able to catch an exception and roll back any changes a procedure made to the collection. But this is a rare case for me. I would almost always prefer the performance improvement from NOCOPY.
The NOCOPY behavior with collections is the exact same behavior that happens by default in most other programming language: changes made to a container inside of a function reflect outside of that function even if the function raises an exception. In most other languages, if you want to do a rollback you would manually copy and return a new function instead.