Oracle and PL/SQL Recipes: A Problem-Solution Approach

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 ...