SQL Developer Data Modeler

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,

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,

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.

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).

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!

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>