Working with Collections in Subprogram Headers

Question:
I have two questions: (1) How I can pass a collection as an argument to a procedure? and (2) How I return a collection from a procedure?
Answer:
I have received this question from a number of people over the years, and I find the question both surprising and understandable.
I am surprised because the syntax for passing a collection as an argument is actually no different than that for passing a string or a date. You simply define the parameter as having a datatype matching the collection type and....off you go!
I understand the question because many PL/SQL developers seem to be generally a bit wary of and confused by collections.
So perhaps more of an explanation is needed. Here we go!
Collections are array-like structures in PL/SQL. With them, you can manage lists of information, build stacks, queues, linked lists, single dimensional arrays, and more. You can emulate multi-dimensional arrays; you can index not only by integers but also by strings, giving you enormous flexibility. With the TABLE operator, you can treat a collection like a relational table inside a query.
In addition, you need to use collections in order to take advantage of two of the most powerful and useful statements in the PL/SQL tool box: FORALL and BULK COLLECT, which dramatically improve performance of multi-row SQL operations from a PL/SQL program.
To sum up, I believe that collections are an absolutely amazing and crucial feature of PL/SQL. All developers should come up to speed on collections, be comfortable using them, and apply them wherever possible.
Now, one way to apply them is to pass them to and from subprograms: procedures and functions. So, like I said above, there is nothing special about passing a collection as an argument. When you define the parameter in your subprogram, you must provide a name, a mode (IN is the default) and the datatype. I think that your question comes down to: how to specify the datatype?
And this can be a little bit tricky, because in most cases you will need to have previously defined the datatype
.
But let's take things a step at a time. Suppose that I want to write a program that accepts a collection of a type that has already been defined, for example, DBMS_SQL.VARCHAR2A. This collection type is defined in the DBMS_SQL package as follows:
type varchar2a is table of varchar2(32767)
index by binary_integer;
It is used for parsing very long SQL statements (those that are greater than 32K in length). But I could also use it as a "generic type" for "list of strings".
Here is an example of using the type in an anonymous block:
DECLARE
my_strings DBMS_SQL.VARCHAR2A;
BEGIN
DBMS_OUTPUT.put_line ('>>> Populate a DBMS_SQL.VARCHAR2A array');
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
END;
/
I declare a list of "my strings" based on the pre-defined collection type, and then populate the list with three values.
Now let's take one more step and create a local subprogram that accepts a collection of this type as an IN argument, and displays its contents:
DECLARE
my_strings DBMS_SQL.varchar2a;

PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN DBMS_SQL.varchar2a)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> DBMS_SQL.VARCHAR2A as IN parameter ' || title_in);

/* I assume below that the collection is densely filled. */
FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
And here, then, you can see how to pass a collection into a procedure. "Just do it!" I can also return a collection through a function. In the following block, I added a function to "double" the values in my list. Notice that I pass in a collection as an IN argument, but also pass a collection of the same type as the RETURN clause of the function.
DECLARE
my_strings DBMS_SQL.varchar2a;
my_strings2 DBMS_SQL.varchar2a;

PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN DBMS_SQL.varchar2a)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> DBMS_SQL.VARCHAR2A as RETURN clause ' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;

FUNCTION double_values (list_in IN DBMS_SQL.varchar2a)
RETURN DBMS_SQL.varchar2a
IS
l_return DBMS_SQL.varchar2a;
BEGIN
FOR indx IN 1 .. list_in.COUNT
LOOP
l_return (l_return.COUNT + 1) :=
list_in (indx) || '-'
|| list_in (indx);
END LOOP;

RETURN l_return;
END double_values;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
my_strings2 := double_values (my_strings);
show_list ('My name - Doubled', my_strings2);
END;
/
Again, note that there is no special syntax required to return a collection; just specify the datatype.
I have now showed you how to use a pre-defined collection type in your parameter list. One last topic to cover: how to create your own collection types and use them in your parameter list. The collection type has to be available in the header of your program, so either it has to be defined:
1. at the schema level as a database object;
2. in the specification of a package on which your schema has execute authority;
3. in the declaration section of the block in which the program is then defined.
I demonstrate each of these below:
1. Define a collection type at the schema level as a database object. This is possible only for nested tables and varrays. Notice that there is very little difference from the original example, except for syntax changes needed to work with nested tables (use of the constructor function, strings_ntt (), and the extend statement).
CREATE OR REPLACE TYPE strings_ntt IS TABLE OF VARCHAR2 (32767)
/

DECLARE
my_strings strings_ntt := strings_ntt ();

PROCEDURE show_list (title_in IN VARCHAR2, list_in IN strings_ntt)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Schema-level collection type ' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings.EXTEND (3);
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
2. Define the collection type in the specification of a package on which your schema has execute authority. I will do, in other words, precisely what Oracle did in the DBMS_SQL package, but for my own type:
CREATE OR REPLACE PACKAGE coll_types_pkg
IS
TYPE strings_aat IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;

TYPE dates_aat IS TABLE OF DATE
INDEX BY PLS_INTEGER;

TYPE booleans_aat IS TABLE OF BOOLEAN
INDEX BY PLS_INTEGER;
END coll_types_pkg;
/

DECLARE
my_strings coll_types_pkg.strings_aat;

PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN coll_types_pkg.strings_aat)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Collection type in package spec ' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
3. Declare the collection type in the declaration section of the block in which the program is then defined. The code below is exactly the same as the original example using DBMS_SQL.VARCHAR2A, but I have my own type statement.
DECLARE
TYPE strings_aat IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;

my_strings strings_aat;

PROCEDURE show_list (title_in IN VARCHAR2, list_in IN strings_aat)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Locally declared collection type' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
To conclude with a best practice recommendation or two:
• Avoid declaring collection types inside anonymous blocks or declaration sections of subprograms. It is very likely that you will need to use the type more than once.
• Define your collection types in the database (with a schema-level CREATE OR REPLACE TYPE) if you need to use that type as a column in a relational table.
• Define your collection types in a package specification whenever you want or need to use an associative array (they support negative indexes, sparsely-filled collections, string indexes), or you are using these collection types solely in PL/SQL code (not as columns in tables.