Forum Stats

  • 3,757,253 Users
  • 2,251,213 Discussions
  • 7,869,779 Comments

Discussions

i need the following plsql concepts

990187
990187 Member Posts: 94
edited Jul 8, 2013 12:44AM in SQL & PL/SQL

Iam facing problem in interviews if anyone ask me about NOCOPY,how to call procedure a from procedure b?Any one plz let me know the following

1)what is  the use of nocopy parameter.anyone please do explain with an example?

2)how to call procedure a from procedure b.Plz explain with an example.

Plz help me out in getting them . . waiting for ur reply . . . .

Tagged:
sgudipudi

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Jul 6, 2013 1:37AM
    990187 wrote:
    
    Iam facing problem in interviews if anyone ask me about NOCOPY,how to call procedure a from procedure b?Any one plz let me know the following
    
    1)what is  the use of nocopy parameter.anyone please do explain with an example?
    2)how to call procedure a from procedure b.Plz explain with an example.
    
    Plz help me out in getting them . . waiting for ur reply . . . .
    
    

    Make the documentation your friend. For questions such as this, there's really no need to go out and solicit help from others. The documentation is quite clear. It's also greatly more efficient for you to be able to find your own answers than to weed through a bunch of replies you're going to get in a forum ... many of which will simply be "RTFM".

    PL/SQL Optimization and Tuning

    Cheers,

  • 34MCA2K2
    34MCA2K2 Member Posts: 442

    If you have understanding of a fundamental programming concept called "pass by reference" you will have no problems in understanding nocopy. To call one P1 procedure from P2 procedure, you can simply write P1(In parameters list, out parameter list); in P2 definition.


    However If you really want to learn Oracle PL/SQL I suggest you follow advice of Tubby


    Regards,

  • HuaMin Chen
    HuaMin Chen Member Posts: 3,578
    990187 wrote:
    
    Iam facing problem in interviews if anyone ask me about NOCOPY,how to call procedure a from procedure b?Any one plz let me know the following
    
    1)what is  the use of nocopy parameter.anyone please do explain with an example?
    2)how to call procedure a from procedure b.Plz explain with an example.
    
    Plz help me out in getting them . . waiting for ur reply . . . .
    

    Check this

    Ask Tom "Anomaly with NOCOPY"

    inside which you would also see the way to call one other SP, within a SP.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond
    990187 wrote:
    
    Iam facing problem in interviews if anyone ask me about NOCOPY,
    

    There are 2 ways to pass a parameter from a caller procedureA to called procedureB.

    Pass by value.

    This means procedureA's variable value for the parameter is copied from procedureA's memory (stack) to procedureB. ProcedureB thus receives a copy of the value. This method is usually the default in programming languages - and works well. Except for dealing with very large values - as that means a slow copy of data from procedureA's stack to procedureB's stack. And lots of memory required for duplicating the value in both stacks.

    Pass by reference.

    This means procedureA's variable itself is passed as a pointer to procedureB. Thus when procedureB reads the parameter value, it reads it directly from procedureA's memory. When procedureB writes to the parameter (when parameter is defined as an output parameter), procedureB writes directly into procedureA's variable in procedureA's memory. The con with this method is that as procedureB is allowed to write into procedureA's stack, it can potentially corrupt procedureA's memory.

    These 2 methods are common throughout most programming languages. In PL/SQL, there is no explicit pointer type (like in C or Pascal for example). So in order to pass by reference, allowing procedureB to write directly into procedureA's variables, procedureA needs to pass its variables to procedureB using the IN OUT NOCOPY clause. This clause tells the PL/SQL engine to pass by the variable by reference (as a pointer).

    sgudipudi
  • sgudipudi
    sgudipudi Member Posts: 219
    BillyVerreynne wrote:
    
    Pass by reference.
    This means procedureA's variable itself is passed as a pointer to procedureB. Thus when procedureB reads the parameter value, it reads it directly from procedureA's memory. When procedureB writes to the parameter (when parameter is defined as an output parameter), procedureB writes directly into procedureA's variable in procedureA's memory. The con with this method is that as procedureB is allowed to write into procedureA's stack, it can potentially corrupt procedureA's memory.
    
    These 2 methods are common throughout most programming languages. In PL/SQL, there is no explicit pointer type (like in C or Pascal for example). So in order to pass by reference, allowing procedureB to write directly into procedureA's variables, procedureA needs to pass its variables to procedureB using the IN OUT NOCOPY clause. This clause tells the PL/SQL engine to pass by the variable by reference (as a pointer).
    

    Thanks Billy for the explanation. I have a question. Let's say procedureB writing the ProcedureA's memory multiple times and evrything goes smoothly and finally ProcedureB gives the output to procedureA, what if ProcedureB had an exception after writing couple of times to ProcedureA's memory? would that rollback to previous(original) value?

    Thanks again!

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond
    edited Jul 8, 2013 12:46AM

    Write sample code to explore such questions and determine the answers.

    E.g.

    SQL> declare
      2          procedure procB( varB in out nocopy integer ) is
      3          begin
      4                  varB := 1;
      5                  raise NO_DATA_FOUND;
      6          end;
      7          procedure procA is
      8                  varA    integer;
      9          begin
    10                  varA := 0;
    11                  procB( varA );
    12          exception when no_data_found then
    13                  dbms_output.put_line( 'after exception: varA='||varA );
    14          end;
    15  begin
    16          ProcA;
    17  end;
    18  /
    after exception: varA=1 PL/SQL procedure successfully completed. SQL>

    And (passing by value):

    SQL> declare
      2          procedure procB( varB in out integer ) is
      3          begin
      4                  varB := 1;
      5                  raise NO_DATA_FOUND;
      6          end;
      7          procedure procA is
      8                  varA    integer;
      9          begin
    10                  varA := 0;
    11                  procB( varA );
    12          exception when no_data_found then
    13                  dbms_output.put_line( 'after exception: varA='||varA );
    14          end;
    15  begin
    16          ProcA;
    17  end;
    18  /
    after exception: varA=0 PL/SQL procedure successfully completed. SQL>
This discussion has been closed.