Oracle PL/SQL/Stored Procedure Function/NOCOPY

Possible speed benefits of NOCOPY

   <source lang="sql">
   

SQL> SQL> CREATE TABLE book (

 2     isbn      CHAR(10) PRIMARY KEY,
 3     category  VARCHAR2(20),
 4     title     VARCHAR2(100),
 5     num_pages NUMBER,
 6     price     NUMBER,
 7     copyright NUMBER(4),
 8     emp1   NUMBER,
 9     emp2   NUMBER,
10     emp3   NUMBER
11   );

Table created. SQL> SQL> SQL> SQL> SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2              VALUES ("1", "Database", "Oracle", 563,.99,99, 1, 2, 3);

1 row created. SQL> SQL> SQL> SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)

 2              VALUES ("2", "Database", "MySQL", 765,.99,99, 4, 5);

1 row created. SQL> SQL> SQL> SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2              VALUES ("3", "Database", "SQL Server",4,.99,01, 6, 7, 8);

1 row created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE CopyFast AS

 2     TYPE BookArray IS TABLE OF book%ROWTYPE;
 3
 4     PROCEDURE Passbook1(p IN BookArray);
 5     PROCEDURE Passbook2(p IN OUT BookArray);
 6     PROCEDURE Passbook3(p IN OUT NOCOPY BookArray);
 7
 8     PROCEDURE Go;
 9   END CopyFast;
10   /

Package created. SQL> SQL> SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY CopyFast AS

 2     PROCEDURE Passbook1(p IN BookArray) IS
 3     BEGIN
 4       NULL;
 5     END Passbook1;
 6
 7     PROCEDURE Passbook2(p IN OUT BookArray) IS
 8     BEGIN
 9       NULL;
10     END Passbook2;
11
12     PROCEDURE Passbook3(p IN OUT NOCOPY BookArray) IS
13     BEGIN
14        NULL;
15     END Passbook3;
16
17     PROCEDURE Go IS
18       v_BookArray BookArray := BookArray(NULL);
19       v_Time1 NUMBER;
20       v_Time2 NUMBER;
21
22      BEGIN
23       
24        SELECT * INTO v_BookArray(1) FROM book WHERE ISBN = "3";
25       v_BookArray.EXTEND(50000, 1);
26
27       v_Time1 := DBMS_UTILITY.GET_TIME;
28       Passbook1(v_BookArray);
29       v_Time2 := DBMS_UTILITY.GET_TIME;
30       Passbook2(v_BookArray);
31
32       
33       DBMS_OUTPUT.PUT_LINE("Time to pass IN: " || TO_CHAR((v_Time2 - v_Time1) /0));
34     END Go;
35   END CopyFast;
36    /

Package body created. SQL> SQL> show errors No errors. SQL> SQL> BEGIN

 2     CopyFast.Go();
 3   END;
 4   /

BEGIN

ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at "sqle.COPYFAST", line 33 ORA-06512: at line 2

SQL> SQL> SQL> SQL> drop table book; Table dropped. SQL> SQL> SQL>

 </source>
   
  

The behavior of NOCOPY.

   <source lang="sql">
   

SQL> SQL> CREATE OR REPLACE PROCEDURE NoCopyTest(p_In IN NUMBER,p_Out OUT NOCOPY VARCHAR2,p_InOut IN OUT NOCOPY CHAR) IS

 2   BEGIN
 3     NULL;
 4   END NoCopyTest;
 5   /

Procedure created. SQL> SQL> SQL> show errors No errors. SQL> SQL> SQL> -- A modified version of RaiseError, with the out parameter specified as NOCOPY. SQL> CREATE OR REPLACE PROCEDURE RaiseErrorNoCopy(p_Raise IN BOOLEAN,pA OUT NOCOPY NUMBER) AS

 2   BEGIN
 3     pA := 5;
 4     IF p_Raise THEN
 5       RAISE DUP_VAL_ON_INDEX;
 6     ELSE
 7       RETURN;
 8     END IF;
 9   END RaiseErrorNoCopy;
10   /

Procedure created. SQL> SQL> show errors No errors. SQL> SQL> -- When we call RaiseErrorNoCopy the exception semantics are changed due to NOCOPY. SQL> DECLARE

 2     v_Num NUMBER := 1;
 3   BEGIN
 4     DBMS_OUTPUT.PUT_LINE("Value before first call: " || v_Num);
 5     RaiseErrorNoCopy(FALSE, v_Num);
 6     DBMS_OUTPUT.PUT_LINE("Value after successful call: " || v_Num);
 7     DBMS_OUTPUT.PUT_LINE("");
 8
 9     v_Num := 2;
10     DBMS_OUTPUT.PUT_LINE("Value before second call: " || v_Num);
11     RaiseErrorNoCopy(TRUE, v_Num);
12   EXCEPTION
13     WHEN OTHERS THEN
14        DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " || v_Num);
15   END;
16   /

Value before first call: 1 Value after successful call: 5 Value before second call: 2 Value after unsuccessful call: 5 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> SQL>

 </source>