i need the following plsql concepts

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,

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

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>