Possible speed benefits of NOCOPY : NOCOPY « Stored Procedure Function « Oracle PL / SQL






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 "JAVA2S.COPYFAST", line 33
ORA-06512: at line 2


SQL>
SQL>
SQL>
SQL> drop table book;

Table dropped.

SQL>
SQL>
SQL>

   
    
    
    
  








Related examples in the same category

1.The behavior of NOCOPY.