Collection Types in PL/SQL

I often see questions on technical forums about arrays in PL/SQL, which type to use and what the differences are. Although the documentation has quite a lot to say on the subject, I feel it tries to introduce too many concepts at once and ends up being confusing, especially when it comes to choosing between VARRAYs and Nested Table collections, where most of the "which type to use" advice is about collection columns of database tables (which you probably don't need), and not about PL/SQL at all. The documentation may give the impression that "array" means "VARRAY", without mentioning the limitations of VARRAYs. This could be one reason why I often see VARRAYs used where Nested Table collections would have been a better choice. I'm therefore going to set out what I consider the main points of each type, from the point of view of a PL/SQL programmer.

PL/SQL has three types of array

Or as they are known in PL/SQL, collection. "Collection" means a program variable containing more than one value in the form of a list. The term "array" has a more specific meaning depending on your programming language and computer science background. According to the PL/SQL documentation at least, other languages provide arrays, sets, bags, linked lists and hash tables. The equivalent types in PL/SQL can all be referred to as "collections", and PL/SQL provides three of them:

Associative Array: type t is table of something index by pls_integer;
Nested Table: type t is table of something;
VARRAY: type t is varray(123) of something;

Now although this is all mentioned in the documentation, and there are many articles and blogs that more or less repeat the documentation (and unhelpfully invent new terms like "Associative Table"), I see two problems:

There are three types of array? Which one do I use?

I'll begin by assuming you're writing some PL/SQL, and you're looking for some sort of variable to use in your code that can hold multiple values and that you access using something like myarray[n].

I need to mention this because Oracle also supports the use of collection types as columns in database tables, and the PL/SQL documentation attempts to describe both this feature and the PL/SQL programming aspects at the same time, so for example when it talks about VARRAYs it sometimes means VARRAY columns in database tables, and sometimes VARRAY variables in PL/SQL code. This can be pretty confusing if you're a programmer looking for an array type and you find the documentation talking about tables, columns, tablespaces, and VARRAYs being stored inline and useful when their elements are usually all accessed in sequence (huh?) and so on. Now, if you really do want a collection column in a database table (...which you probably don't...) then VARRAYs could well be just the thing. If you're not, though, they aren't.

So, setting aside the whole idea of collection columns in database tables, and just looking at PL/SQL programming, here are what I see as the main differences.

Collection types as table columns

I did just say I wasn't going to cover using collection types as columns (because why would you?) but as a quick note:

Scope: SQL or PL/SQL

The biggest difference is to do with where you can use them. SQL and PL/SQL are two separate languages, with PL/SQL the procedural wrapper for SQL. Associative Arrays exist only in PL/SQL, and SQL doesn't know anything about them. So, the first distinction to make is between the one PL/SQL-only type and the other two:

Before we go into that, however, it's worth mentioning the difference between creating a type in SQL and declaring a type in PL/SQL.

Collection types created in SQL

Collections are part of the SQL language in Oracle, so you use them without even going into PL/SQL:

create type varchar2_tt as table of varchar2(100)
/

Type created.

select column_value as val
from   table(varchar2_tt('Apple','Banana','Apricot'))
where  column_value not like 'A%';

VAL
--------------------
Banana

1 row selected.

We created a collection type and used it in a query, all in SQL. Types created in this way can be used in any query and any PL/SQL program (for other user accounts to use them, you need to GRANT EXECUTE ON type_name TO other_account).

Collection types declared in PL/SQL

Within your PL/SQL programs, you can either use existing types that were created in SQL (like the varchar2_tt example above) or you can declare your own types locally. Declaring them within PL/SQL gives you a lot of flexibility to define custom types for use only within a particular procedure or package, and also lets you anchor them to a particular table or column's definition using the %TYPE and %ROWTYPE syntax (for example, TABLE OF emp.empno%TYPE will take its definition from emp.empno, whatever that is at the time you compile the PL/SQL code - this is a PL/SQL feature not available within SQL). You can even declare your own record types and then declare a collection of that record type, for example:

declare
   type stats_rec is record
   ( batch_step      batch_log.step_name%type
   , elapsed_time    interval day(0) to second(0)
   , rows_processed  pls_integer );

    type stats_tt is table of stats_rec; -- a locally-defined nested table collection type

However, since they exist only in PL/SQL, SQL doesn't know about them and can't use them, and so you can't use them in queries. In the earlier example, if we had declared varchar2_tt within a PL/SQL package, we could not use it in a query:

create or replace package testtypes as
   type varchar2_tt is table of varchar2(100);
end testtypes;
/

Package created.

select column_value AS val
from   table(testtypes.varchar2_tt('Apple','Banana','Apricot'))
where  column_value not like 'A%';


from   table(testtypes.varchar2_tt('Apple','Banana','Apricot'))
       *
ERROR at line 2:
ORA-22905: cannot access rows from a non-nested table item

The SQL query is exactly the same as the previous example, except that we attempted to use testtypes.varchar2_tt (a type declared in a PL/SQL package) instead of varchar2_tt (a type declared in SQL). What the error message is saying there is that testtypes.varchar2_tt is not a nested table type it knows about, so the TABLE() expression is not valid. Of course, it is a nested table type within PL/SQL, but SQL doesn't know PL/SQL.

Like any database object, you can query the data dictionary to see what collection types are available:

select ct.owner, ct.type_name, ct.elem_type_name, ct.length
from   all_coll_types ct
where  ct.coll_type = 'TABLE'
and    ct.elem_type_owner is null
order by ct.owner, ct.type_name;


OWNER                TYPE_NAME                  ELEM_TYPE_NAME               LENGTH
-------------------- -------------------------- ------------------------- ---------
DMSYS                DMCLAOS                    DMCLAO
DMSYS                DMCLBOS                    DMCLBO
DMSYS                DM_ITEMS                   VARCHAR2                       4000
DMSYS                ORA_MINING_NUMBER_NT       NUMBER
DMSYS                ORA_MINING_VARCHAR2_NT     VARCHAR2                       4000
EXFSYS               RLM$DATEVAL                TIMESTAMP
EXFSYS               RLM$KEYVAL                 VARCHAR2                       1000
EXFSYS               RLM$NUMVAL                 NUMBER
EXFSYS               RLM$ROWIDTAB               VARCHAR2                         38
MDSYS                RDF_MODELS                 VARCHAR2                         25
MDSYS                RDF_RULEBASES              VARCHAR2                         25
MDSYS                SDO_NUMTAB                 NUMBER
MDSYS                SDO_TOPO_NSTD_TBL          NUMBER
SYS                  DBMS_AW$_COLUMNLIST_T      VARCHAR2                        100
SYS                  DBMS_DEBUG_VC2COLL         VARCHAR2                       1000
SYS                  KU$_OBJNUMSET              NUMBER
SYS                  KU$_VCNT                   VARCHAR2                       4000
SYS                  ORA_DM_TREE_NODES          ORA_DM_TREE_NODE
WILLIAM              DATE_TT                    DATE
WILLIAM              INTEGER_TT                 INTEGER
WILLIAM              NUMBER_TT                  NUMBER
WILLIAM              SUDOKU_CELL_TT             NUMBER
WILLIAM              VARCHAR2_TT                VARCHAR2                       4000

Notice that SYS.DBMS_DEBUG_VC2COLL is a VARCHAR2(1000) collection, which can be handy in an environment where you cannot create your own new types.

Special Features of Associative Arrays

Associative Arrays are convenient if you just need a declare-and-go type to use within some PL/SQL code. No separate CREATE OR REPLACE TYPE to put through release control, no initialising, no extending, and you can just put values anywhere you like in them: 

declare
   type number_index_by_string is table of number index by varchar2(30);
   type string_index_by_number is table of dept.loc%type index by pls_integer;

   v_country_codes  number_index_by_string;
   v_countries      string_index_by_number;
begin
   v_country_codes('Ukraine') := 380;
   v_country_codes('UAE')     := 971;
   v_country_codes('UK')      := 44;
   v_country_codes('USA')     := 1;

   v_countries(380)  := 'Ukraine';
   v_countries(971)  := 'UAE';
   v_countries(44)   := 'UK';
   v_countries(1)    := 'USA';
end;

Notice also we can INDEX BY a character string. In the example above, we declared number_index_by_string using INDEX BY VARCHAR2(30), which means we can refer to its 'UK' element as v_country_codes('UK'). This is a unique feature of Associative Arrays. (In fact it's the point of them - they associate pairs of values such as 'UK' and 44. In the other collection types the index is just a locator and doesn't have any meaning itself.) You can use this to access array values by their business key: for example, if we had a variable v_country VARCHAR2(10), we could find the corresponding element in the array using v_country_codes(v_country). (We can also use the more usual numeric approach, INDEX BY PLS_INTEGER.)

Associative Arrays don't have constructors. This means you can't use the syntax myarray := mytype(2,4,6,37) to populate your collection with four values at once, as you can with the other types. Although this may seem an invonvenient limitation, it's consistent with their purpose as a means of associating pairs of values. Since the index value is significant (44 = 'UK', 380 = 'Ukraine' etc), it would be wrong for the PL/SQL compiler to just default them sequentially as 1, 2, 3 and so on.

SQL vs PL/SQL collection types: summary

You can declare any type you like within PL/SQL, and you get a lot of flexibility that way such as the use of PL/SQL-only language features, but remember that SQL does not know PL/SQL, and so you cannot use your PL/SQL types in SQL queries.

The other main differences are listed below.

Scope What that means Collection Types

PL/SQL

Declared only in PL/SQL code - no "CREATE OR REPLACE TYPE". SQL doesn't know anything about them.

No initialisation or extending required - just assign values to any arbitrary element, doesn't even have to be consecutive.

You can choose what to "index by" - PLS_INTEGER, BINARY_INTEGER1 The same thing as PLS_INTEGER, but with a slightly longer name.

Oracle introduced PLS_INTEGER as a new PL/SQL performance feature in version 7, with a note in the documentation urging you to use it in place of the older BINARY_INTEGER in all new development. For ten years this advice went largely ignored, because for one thing you still couldn't use it for INDEX BY collections, which remained stuck with the old BINARY_INTEGER along with most Oracle-supplied packages. Finally in 9i we could INDEX BY PLS_INTEGER but most people had got so used to INDEX BY BINARY_INTEGER that they kept on using it, and in any case there didn't seem to be any noticeable performance difference between the two. It was rumoured that Oracle had actually made everything PLS_INTEGER under the covers anyway, and this became official in 10g. or VARCHAR2.

No constructor - you must assign values explicitly.

Can't treat as a table in queries, e.g. you cannot SELECT * FROM TABLE(myarray)

Associative Array

SQL and PL/SQL

Declared either in PL/SQL code or with "CREATE OR REPLACE TYPE".

Must be initialised before use, e.g. myarray mytype := mytype();

Have constructors - you can assign values using mytype('x','y','z');

Must be extended as required, e.g. myarray.EXTEND; to add each array element.

Can treat as a table in queries e.g. SELECT * FROM TABLE(myarray) (if created in SQL with CREATE TYPE).

Nested Table

VARRAY

So what's wrong with VARRAYs?

From reading the PL/SQL documentation, you might get the impression that VARRAYs and Nested Tables are pretty similar. After all, you can use either type in SQL queries using TABLE() expressions as well as in PL/SQL code, and most of the differences listed in the section Choosing Between Nested Tables and Varrays are about storage considerations for collection columns of database tables (for example, "Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB)") - none of which is relevant when choosing an array type for a PL/SQL program.

Worse, in my view, are the statements:

Now this is doubtless true, depending on what other languages you are used to, but if (like me) you've never come across sets or bags before, or perhaps you have used arrays in a language like JavaScript or Korn Shell that doesn't make this kind of fancy distinction, and you just want some sort of multi-valued variable to use in PL/SQL code, the above statements might make you think a VARRAY is probably what you want (it's an array, right?) when it is not.

VARRAYs lack some of the functionality you get with nested table collections in 10g. The following work only with nested table collections, not with VARRAYs.

For example, try these using the varchar2_tt nested table collection type we created earlier:

declare
   my_array varchar2_tt :=
      varchar2_tt('Apple','Apple','Orange','Banana');
begin
   if my_array is a set then
      dbms_output.put_line('No duplicates found');
   else
      dbms_output.put_line('Collection contains duplicates');
   end if;
end;

Collection contains duplicates


declare
   my_array varchar2_tt :=
      varchar2_tt('Apple','Apple','Orange','Banana');
begin
   if 'Orange' member of my_array then
      dbms_output.put_line('"Orange" exists in the collection');
   else
      dbms_output.put_line('"Orange" does not exist in the collection');
   end if;
end;

"Orange" exists in the collection


declare
    my_array1 varchar2_tt :=
        varchar2_tt('Apple','Orange','Cherry','Banana');

    my_array2 varchar2_tt :=
        varchar2_tt('Orange','Kumquat','Grape','Banana');

    my_array3 varchar2_tt := my_array1 multiset intersect my_array2;
begin
    for i in my_array3.first..my_array3.last loop
        dbms_output.put_line(my_array3(i));
    end loop;
end;

Orange
Banana

That could save you a lot of code if you want to check whether a collection's values are unique, or whether a particular value exists in the collection, or to find the values common to two collections. As you'll see from the links above, these are just a few of the useful collection features to explore.

Now let's try the same thing with a VARRAY instead of a nested table collection:

create type varchar2_vtt as varray(100) of varchar2(100)
/

Type created.


declare
   my_array varchar2_vtt :=
      varchar2_vtt('Apple','Apple','Orange','Banana');
begin
   if my_array is a set THEN
      dbms_output.put_line('No duplicates found');
   else
      dbms_output.put_line('Collection contains duplicates');
   end if;
end;

if my_array is a set then
   *
ERROR at line 5:
ORA-06550: line 5, column 8:
PLS-00306: wrong number or types of arguments in call to 'IS A SET'
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored

And so on. In fact, none of the collection functions, multiset conditions or operators work with VARRAYs. That is a huge amount of useful functionality to lose just because you chose the wrong collection type.

Summary

I have deliberately not covered the use of collection types as columns of database tables or views, because I wanted to focus on PL/SQL programming, and a whole discussion of database table design would just would have complicated things. The PL/SQL documentation attempts to cover collection columns as well as PL/SQL collection variables in the same section, and in my opinion this causes confusion.2 I should mention here that in general the Oracle documentation is pretty great. VARRAYs could well come into their own as a database column type for small lists of values - although whether it is ever a good idea to use this approach is another question.

VARRAYs are generally only useful when you are working with actual VARRAY columns of database tables, or when the LIMIT attribute is overwhelmingly useful in enforcing some business rule. Otherwise they are just a functionally crippled version of nested table collections, with a LIMIT clause you don't need.

Update for 12c

This article was written for 10g/11g. Since then, 12c has removed some restrictions, and collections defined in PL/SQL packages can now be used in SQL. The updated documentation is also clearer, although it still mentions under "Appropriate Uses for Varrays" that a varray is appropriate when "You usually access the elements sequentially." This is doubtless technically correct, but as a PL/SQL developer I still don't know what it means and it surely has little to do with normal array variables in package code.

Further reading