Oracle PL/SQL/Stored Procedure Function/NOCOPY

Possible speed benefits of NOCOPY

    
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>

The behavior of NOCOPY.

    
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>