This discussion is archived
4 Replies Latest reply: May 3, 2013 5:38 AM by BS2012 RSS

NOCOPY option in parameters

BS2012 Newbie
Currently Being Moderated
Hi Everyone,
I found this "NOCOPY" option has been used a lot in so many stored procedures. As I googled, I found some facts and features as mentioned below.
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. Because a copy of the parameter set was made, rollback could be done. However, this method
imposed significant CPU and memory overhead when the parameters were large data collectionsfor example, PL/SQL Table or VARRAY types. 
With the new NOCOPY option, OUT and IN OUT parameters are passed by reference, which avoids copy overhead. However, parameter set
copy is not created and, in case of an exception rollback, cannot be performed and the original values of parameters cannot be restored.
So if this is true, what benefit we will be getting by using this option. I mean, in case of a failure transaction we will not be able to rollback for those
variables or parameters declared with NOCOPY option? If it is performance related, Ok but we can't rollback in case of any error.


Regards,
BS2012
  • 1. Re: NOCOPY option in parameters
    LPS Journeyer
    Currently Being Moderated
    Find the link for NOCOPY documentation given by oracle

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/parameter_declaration.htm#sthref1179
  • 2. Re: NOCOPY option in parameters
    BluShadow Guru Moderator
    Currently Being Moderated
    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/39416

    >
    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).
  • 3. Re: NOCOPY option in parameters
    AlbertoFaenza Expert
    Currently Being Moderated
    You can have a simple example with this code:

    Without using nocopy in case of exception values of IN/OUT parameters are 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.
    Using NOCOPY variable a is not rollbacked:
    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.
    Regards.
    Al
  • 4. Re: NOCOPY option in parameters
    BS2012 Newbie
    Currently Being Moderated
    Thanks a lot guys for your explanation and demonstration.

    Regards,
    BS2012

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points