13-4. Passing Large or Complex Collections as OUT Parameters
Problem
You have a procedure or function that accepts one or more large or complex collections that are also OUT parameters, and you need a more efficient method to pass these variables.
Solution
Pass the parameters to your procedure or function by reference using the NOCOPY option on the procedure or function declaration.
CREATE OR REPLACE PACKAGE no_copy_test AS
TYPE rec_type IS TABLE OF all_objects%ROWTYPE INDEX BY BINARY_INTEGER;
PROCEDURE test;
END no_copy_test;
/
show error
CREATE OR REPLACE PACKAGE BODY no_copy_test AS
PROCEDURE proc1 (rec_list IN OUT rec_type) IS
BEGIN
FOR i IN 1..rec_list.COUNT LOOP
rec_list(i) := rec_list(i);
END LOOP;
END;
PROCEDURE proc2 (rec_list IN OUT NOCOPY rec_type) IS
BEGIN
FOR i IN 1..rec_list.COUNT LOOP
rec_list(i) := rec_list(i);
END LOOP;
END;
PROCEDURE test IS
CURSOR driver IS
SELECT *
FROM all_objects;
recs rec_type;
rec_count integer;
BEGIN
OPEN driver;
FETCH DRIVER BULK COLLECT INTO recs;
CLOSE driver;
rec_count := recs.COUNT;
DBMS_OUTPUT.PUT_LINE (systimestamp);
proc1 (recs); -- parameter passed by value
DBMS_OUTPUT.PUT_LINE (systimestamp);
proc2 (recs); -- paramter passed by reference
DBMS_OUTPUT.PUT_LINE (systimestamp);
END test;
END no_copy_test;
set serverout on -- Enable output from DBMS_OUTPUT statements
EXEC no_copy_test.test;
Running the procedure produced the following output:
03-NOV-10 05.05.14.865000000 PM -05:00
03-NOV-10 05.05.14.880000000 PM -05:00
03-NOV-10 05.05.14.880000000 PM -05:00
How It Works
The recipe utilizes the NOCOPY feature within PL/SQL. It begins by defining two procedures within the test package. The first procedure, PROC1, accepts a collection of records using the default parameter-passing method, which is by VALUE. The second procedure, PROC2, is an exact copy of PROC1; however, its parameter is passed using the NOCOPY option. In PROC1, the parameter is passed in by VALUE, which means a copy of the entire collection is created in the REC_LIST variable within PROC1. In PROC2, the parameter data is passed by REFERENCE. Passing a parameter by reference does not copy the data; rather, it uses the existing data structure passed to it by the calling program. This method is more efficient for very large collections in both running time and in memory usage.
The output from the test shows the first procedure, which passed its parameter by VALUE took longer to run than the second procedure, which passed its parameter by REFERENCE. In this example, the USER_OBJECTS table was used as the data for the parameter, which retrieved only 6,570 records. Larger performance gains can be realized with more records and more complex data structures.