OracleBlog

What is NOCOPY?

'NOCOPY' is an optional 'hint' to tell the PL/SQL 'compiler' not to go through the overhead of making a copy of the variable, instead just send a reference. This is generally because we don't plan on modifying it within the procedure.

My first surprise was that you couldn't use "IN NOCOPY." Isn't NOCOPY your way of telling Oracle you don't plan on messing around with the parameter? Yes, but you CAN'T mess with IN parameters, try it!

CREATE OR REPLACE PROCEDURE MyProc (in_value IN number)
AS
BEGIN
in_value := 3;
END;

PLS-00363: expression 'IN_VALUE' cannot be used as an assignment target

Therefore, it is always safe to send IN parameters by reference, making NOCOPY redundant.

My second surprise was that you had to specify NOCOPY for an OUT parameter. Because by definition isn't an OUT parameter stating that you plan on modifying the variable? Why would it be an OUT variable if you weren't touching it? So why would you NOT want NOCOPY? The answer (like so many) comes from Ask Tom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2047154868085

Tom explains one situation where you want a copy rather than a reference for an OUT or IN OUT parameter. When you change a NOCOPY parameter, it changes right away, instead of upon successful completion of the stored procedure.

Imagine you modified the parameter, but threw an exception before successful completion. But that parameter has been changed and the calling procedure could be stuck with a bogus value.

Despite how much I trust Tom, everybody knows that I don't believe things until I see for myself. And neither should you! Besides, things change. Here's my example.

CREATE OR REPLACE PROCEDURE NoCopyProc (in_value IN OUT NOCOPY number)
AS
x number;
BEGIN
DBMS_OUTPUT.PUT_LINE(in_value || ' NoCopyProc');
in_value := 2;
x := 1/0;
END;

CREATE OR REPLACE PROCEDURE CopyProc (in_value IN OUT number)
AS
x number;
BEGIN
DBMS_OUTPUT.PUT_LINE(in_value || ' CopyProc');
in_value := 4;
x := 1/0;
END;

CREATE OR REPLACE PROCEDURE InterProc (in_value IN OUT NOCOPY number)
AS
BEGIN
IF (in_value = 1) THEN NoCopyProc(in_value);
ELSE CopyProc(in_value);
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;

CREATE OR REPLACE PROCEDURE MyProc
AS
the_value NUMBER(1);
BEGIN
the_value := 1;
InterProc(the_value);
DBMS_OUTPUT.PUT_LINE(the_value);

the_value := 3;
InterProc(the_value);
DBMS_OUTPUT.PUT_LINE(the_value);
END;

BEGIN MyProc; END;

1 NoCopyProc
2
3 CopyProc
3

For an excellent and more detailed overview of NOCOPY, complete with examples, restrictions and performance analysis, I once again refer you to Steven Feuerstein's writings. Although I encourage you to add his books to your collection, this chapter happens to be on-line for free:

Oracle PL/SQL Programming Guide to Oracle8i Features
http://www.unix.org.ua/orelly/oracle/guide8i/ch10_01.htm

So what is a guy to do?

Well, first of all, it was suggested to me that I should find a more gender-neutral way of summing up an article. Allow me to rephrase.

So what should we do?

1. Understand what NOCOPY means and its uses and restrictions (by following those links)
2. Take advantage of NOCOPY when you want the performance advantage of avoiding the cost of the temporary storage for OUT or IN OUT parameters.
3. Avoid NOCOPY when you don't want the side effects if the procedure fails early.

Remember, in the end, that NOCOPY is just a "hint" and Oracle will do whatever it wants anyway. Like all hints, you have to ask yourself what makes it necessary, and what makes you think Oracle is going to choose incorrectly.