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

10-7. Passing a Collection As a Parameter

Problem

You want to pass a collection as a parameter to a procedure or function. For example, you have a data structure that contains employee data, and you need to pass the data to a routine that prints employee paychecks.

Solution

Create a collection of employee records to hold all employee data, and then pass the data to the subroutine to process the paychecks. The TYPE statement defining the data structure must be visible to the called procedure; therefore, it must be defined globally, prior to defining any procedure or function that uses it.

In this example, employee data is fetched from the database into a collection and then passed to a subroutine for processing.

set serverout on size 1000000 ...