Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle 11.2 PL-SQL Language Reference - NOCOPY Example 8–17

Jibba JabbaApr 26 2013 — edited Apr 26 2013
Hello,

The following is from the Oracle PL/SQL reference, page 8-19:
In Example 8–17, the procedure has an IN OUT NOCOPY formal parameter, to which it assigns the value 'aardvark'. 
The anonymous block assigns the value 'aardwolf' to a global variable and then passes the global variable to the procedure. 
If the compiler obeys the NOCOPY hint, then the final value of the global variable is 'aardvark'. 
If the compiler ignores the NOCOPY hint, then the final value of the global variable is 'aardwolf'.

DECLARE
    TYPE Definition IS RECORD (word VARCHAR2(20),meaning VARCHAR2(200));
    TYPE Dictionary IS VARRAY(2000) OF Definition;
    lexicon Dictionary := Dictionary(); -- global variable
    PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary -- formal NOCOPY parameter)
    IS
    BEGIN
        word_list(1).word := 'aardvark';
    END;
BEGIN
    lexicon.EXTEND;
    lexicon(1).word := 'aardwolf';
    add_entry(lexicon); -- global variable is actual parameter
    DBMS_OUTPUT.PUT_LINE(lexicon(1).word);
END;
/

Result:
aardvark
I am confused. I would expect the output to be aardvark regardless of whether NOCOPY was obeyed or not. To test this, I removed the NOCOPY hint and as expected the output was the same. Of course, it should
be the same, because you're saying: "I'm going to pass you a variable that you can change inside the program, so I expect the actual parameter passed to reflect that change outside of the program".
NOCOPY doesn't change that, does it?
What am I missing?

Thanks,
Jason
This post has been answered by gaverill on Apr 26 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 24 2013
Added on Apr 26 2013
15 comments
789 views