This content has been marked as final. Show 4 replies
Find the link for NOCOPY documentation given by oracle1 person found this helpful
It's useful if you say where you got the information.. I'm guessing it was from this forum: http://www.geekinterview.com/question_details/394161 person found this helpful
The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised,
so that changes could be rolled back
whilst it's true that there is a kind of 'protection' of original values when exceptions are raised, I don't believe this was the main reason that IN, OUT and IN OUT parameters were implemented.
If you go back to the early days of programming, using machine code and assembly language, values were passed between 'sub programs' by pushing things on to the internal stack, before making the execution jump to the desired execution section of code, which would then pop things off the stack, run, and then possibly push things back on the stack before returning execution back to the calling code which could pop the returned things back off again.
Now, the things that are pushed on and popped off the stack, could vary. They could be either explicit values or they could be memory addresses. As the stack was typically limited in size, it was usual that you would pass values that were simple things like numbers directly via the stack, but if there was a need to pass a large piece of data like a string of characters, you would actually pass the address that pointed to that data.
As procedural programming languages developed, they still kept this concept of passing values and/or addresses between procedures. In Oracle, typically, things are passed by value, so a copy is popped onto the stack (and I'll use the term 'stack' loosely now, as the actual implementation within Oracle may contain other layers), and then popped off by the receiving procedure to be used, and if they are an OUT parameter, then the values are popped back on before execution returns to the calling code, where they get popped off and copied over the original (and similarly with return values of functions). There are some exceptions like CLOB's that are not passed by value. With PL/SQL they also provide the NOCOPY keyword (for use with OUT and IN OUT), and this indicates that rather than passing a copy of the value via the stack, an address pointing to the original value is passed instead, so if the 'parameter' is updated, the original source also get's updated.
Now I know from experience that the actual implementation by Oracle within PL/SQL doesn't match exactly what was done in machine code/assembly, and that's because Oracle has implemented other 'features' around the exception handling.
The best thing for you to see how such values get updated and/or rolled back etc. is to try some simple examples yourself (I don't have time to knock up an example just now).
You can have a simple example with this code:1 person found this helpful
Without using nocopy in case of exception values of IN/OUT parameters are rollbacked:
Using NOCOPY variable a is not rollbacked:
SQL> DECLARE 2 a NUMBER; 3 b NUMBER; 4 PROCEDURE divbyzero 5 ( 6 p_a IN OUT NUMBER 7 , p_b OUT NUMBER 8 ) IS 9 BEGIN 10 p_a := p_a/2; 11 DBMS_OUTPUT.PUT_LINE('p_a variable inside procedure: '||p_a); 12 -- unhandled exception 13 p_b := p_a/0; 14 END divbyzero; 15 16 BEGIN 17 a := 10; 18 DBMS_OUTPUT.PUT_LINE('a variable before calling the procedure: '||a); 19 divbyzero(a,b); 20 EXCEPTION 21 WHEN ZERO_DIVIDE THEN 22 DBMS_OUTPUT.PUT_LINE('a variable without NOCOPY after exception: '||a); 23 END; 24 / a variable before calling the procedure: 10 p_a variable inside procedure: 5 a variable without NOCOPY after exception: 10 PL/SQL procedure successfully completed.
SQL> DECLARE 2 a NUMBER; 3 b NUMBER; 4 PROCEDURE divbyzero 5 ( 6 p_a IN OUT NOCOPY NUMBER 7 , p_b OUT NUMBER 8 ) IS 9 BEGIN 10 p_a := p_a/2; 11 DBMS_OUTPUT.PUT_LINE('p_a variable inside procedure: '||p_a); 12 -- unhandled exception 13 p_b := p_a/0; 14 END divbyzero; 15 16 BEGIN 17 a := 10; 18 DBMS_OUTPUT.PUT_LINE('a variable before calling the procedure: '||a); 19 divbyzero(a,b); 20 EXCEPTION 21 WHEN ZERO_DIVIDE THEN 22 DBMS_OUTPUT.PUT_LINE('a variable using NOCOPY after exception: '||a); 23 END; 24 / a variable before calling the procedure: 10 p_a variable inside procedure: 5 a variable using NOCOPY after exception: 5 PL/SQL procedure successfully completed.
Thanks a lot guys for your explanation and demonstration.