NOCOPY

Using NOCOPY With Parameters

DECLARE
 TYPE emptabtyp IS TABLE OF employees%ROWTYPE;
 emp_tab  EMPTABTYP := Emptabtyp(NULL); -- initialize
 t1       NUMBER;
 t2       NUMBER;
 t3       NUMBER;
 PROCEDURE Get_time
      (t  OUT NUMBER)
 IS
 BEGIN
   t := dbms_utility.get_time;
 END;
 PROCEDURE Do_nothing1
      (tab  IN OUT EMPTABTYP)
 IS
 BEGIN
   NULL;
 END;
 PROCEDURE Do_nothing2
      (tab  IN OUT NOCOPY EMPTABTYP)
 IS
 BEGIN
   NULL;
 END;
BEGIN
 SELECT *
 INTO   Emp_tab(1)
 FROM   employees
 WHERE  employee_id = 100;
 emp_tab.Extend(49999,1); -- copy element 1 into 2..50000
 Get_time(t1);
 Do_nothing1(emp_tab); -- pass IN OUT parameter
 Get_time(t2);
 Do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
 Get_time(t3);
 dbms_output.Put_line('Call Duration (secs)');
 dbms_output.Put_line('--------------------');
 dbms_output.Put_line('Just IN OUT: '
                      ||To_char((t2
                                   - t1)
                                  / 100.0));
 dbms_output.Put_line('With NOCOPY: '
                      ||To_char((t3
                                   - t2))
                          / 100.0);
END;
/  

OUTPUT:-

Call Duration (secs)
--------------------
Just IN OUT: .16
With NOCOPY: 0

PL/SQL procedure successfully completed.

DECLARE

 TYPE definition IS RECORD(word VARCHAR2(20),

                            meaning VARCHAR2(200));

 TYPE dictionary IS VARRAY(2000) OF DEFINITION;

 lexicon  DICTIONARY := Dictionary();

 PROCEDURE Add_entry

      (word_list  IN OUT NOCOPY DICTIONARY)

 IS

 BEGIN

   Word_list(1).word := 'aardvark';

 

   Lexicon(1).word := 'aardwolf';

 END;

BEGIN

 lexicon.extend;



 Add_entry(lexicon);



 dbms_output.Put_line(Lexicon(1).word);

END;

/
OUTPUT :- 
aardwolf

PL/SQL procedure successfully completed.

Multilevel Associative Array
DECLARE
TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
-- the following is index-by table of index-by tables
TYPE ntb1 IS TABLE OF tb1 INDEX BY PLS_INTEGER;
TYPE va1 IS VARRAY(10) OF VARCHAR2(20);
-- the following is index-by table of varray elements
TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;
v1 va1 := va1('hello', 'world');
v2 ntb1;
v3 ntb2;
v4 tb1;
v5 tb1; -- empty table
BEGIN
v4(1) := 34;
v4(2) := 46456;
v4(456) := 343;
v2(23) := v4;
v3(34) := va1(33, 456, 656, 343);
-- assign an empty table to v2(35) and try again
v2(35) := v5;
v2(35)(2) := 78; -- it works now
END;
/
Multilevel Nested Table
DECLARE
  TYPE tb1 IS TABLE OF VARCHAR2 (20);

  TYPE ntb1 IS TABLE OF tb1;                      -- table of table elements

  TYPE tv1 IS VARRAY (10) OF INTEGER;

  TYPE ntb2 IS TABLE OF tv1;                     -- table of varray elements

  vtb1    tb1  := tb1 ('one', 'three');
  vntb1   ntb1 := ntb1 (vtb1);
  vntb2   ntb2 := ntb2 (tv1 (3, 5), tv1 (5, 7, 3));
                                                 -- table of varray elements
BEGIN
  vntb1.EXTEND;
  vntb1 (2) := vntb1 (1);
-- delete the first element in vntb1
  vntb1.DELETE (1);
-- delete the first string from the second table in the nested table
  vntb1 (2).DELETE (1);
END;
/