7/8/2020 Collection Types in PL/SQL
Collection Types in PL/SQL
I often see questions on technical forums about arrays in PL/SQL, which type to use and what
the di erences 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.
Update for 12c
This article was written for 10g/11g. Since then, 12c has removed some
restrictions, and collections de ned 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.
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. The word "array" has a more speci c 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:
The documentation can be confusing by mixing up types for tabke columns with types
for PL/SQL variables, and I think it makes the wrong assumptions about what you might
be looking for. Nobody knows what a bag is.
https://www.williamrobertson.net/documents/collection-types.html 1/10
7/8/2020 Collection Types in PL/SQL
It does not mention that VARRAYs lack some major functionality when compared with
Nested Tables in 10g.
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 nd 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 di erences.
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:
A nested table column is always implemented as a separate table internally.
Oracle hides the details and presents it as a multi-valued column, but really
it's a separate table and you'll be automatically joining to it whenever you
query the column.
A VARRAY column will be stored within the table if its values are small (less
than 4K as of 12c, though check the documentation for your release). Oracle
implements it internally as a raw string and parses it for you, to present it as
a multi-valued column. This might actually be a useful feature when you
want to store some small list of values - say, the regions authorised to sell
each product. The kind of thing where someone less database-savvy might
suggest the dreaded comma-separated list. Nearly always, a normalised
data model will do this better, but perhaps for a reporting table in a data
warehouse, there might be some scenario where this could save you a join.
Scope: SQL or PL/SQL
The biggest di erence 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
https://www.williamrobertson.net/documents/collection-types.html 2/10
7/8/2020 Collection Types in PL/SQL
only in PL/SQL, and SQL doesn't know anything about them. So, the rst distinction to make is
between the one PL/SQL-only type and the other two:
Only in PL/SQL: Associative Array
Can also use in SQL: Nested Table, VARRAY
Before we go into that, however, it's worth mentioning the di erence 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 exibility to de ne custom types for use only within a
particular procedure or package, and also lets you anchor them to a particular table or
column's de nition using the %TYPE and %ROWTYPE syntax (for example, TABLE OF
emp.empno%TYPE will take its de nition 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 coll
https://www.williamrobertson.net/documents/collection-types.html 3/10
7/8/2020 Collection Types in PL/SQL
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 LE
-------------------- -------------------------- ------------------------- -----
DMSYS DMCLAOS DMCLAO
DMSYS DMCLBOS DMCLBO
DMSYS DM_ITEMS VARCHAR2
DMSYS ORA_MINING_NUMBER_NT NUMBER
DMSYS ORA_MINING_VARCHAR2_NT VARCHAR2
EXFSYS RLM$DATEVAL TIMESTAMP
EXFSYS RLM$KEYVAL VARCHAR2
EXFSYS RLM$NUMVAL NUMBER
EXFSYS RLM$ROWIDTAB VARCHAR2
MDSYS RDF_MODELS VARCHAR2
MDSYS RDF_RULEBASES VARCHAR2
MDSYS SDO_NUMTAB NUMBER
MDSYS SDO_TOPO_NSTD_TBL NUMBER
SYS DBMS_AW$_COLUMNLIST_T VARCHAR2
SYS DBMS_DEBUG_VC2COLL VARCHAR2
SYS KU$_OBJNUMSET NUMBER
SYS KU$_VCNT VARCHAR2
SYS ORA_DM_TREE_NODES ORA_DM_TREE_NODE
https://www.williamrobertson.net/documents/collection-types.html 4/10
7/8/2020 Collection Types in PL/SQL
WILLIAM DATE_TT DATE
WILLIAM INTEGER_TT INTEGER
WILLIAM NUMBER_TT NUMBER
WILLIAM SUDOKU_CELL_TT NUMBER
WILLIAM VARCHAR2_TT VARCHAR2
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 nd 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 signi cant (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
https://www.williamrobertson.net/documents/collection-types.html 5/10
7/8/2020 Collection Types in PL/SQL
You can declare any type you like within PL/SQL, and you get a lot of exibility 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 di erences are listed below.
Scope What that means Collection Types
PL/SQL Declared only in PL/SQL code - no "CREATE OR REPLACE TYPE". Associative Array
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_INTEGER 1 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)
SQL and PL/SQL Declared either in PL/SQL code or with "CREATE OR REPLACE Nested Table
TYPE".
Must be initialised before use, e.g. myarray mytype
mytype();
Have constructors - you can assign values using
mytype('x','y','z');
VARRAY
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).
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 di erences 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:
Arrays in other languages become varrays in PL/SQL.
Sets and bags in other languages become nested tables in PL/SQL.
https://www.williamrobertson.net/documents/collection-types.html 6/10
7/8/2020 Collection Types in PL/SQL
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.
Collection Functions such as CARDINALITY and SET
Multiset Conditions such as IS A SET, MEMBER OF and SUBMULTISET
Multiset Operators such as MULTISET INTERSECT
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;
https://www.williamrobertson.net/documents/collection-types.html 7/10
7/8/2020 Collection Types in PL/SQL
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 nd 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 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
https://www.williamrobertson.net/documents/collection-types.html 8/10
7/8/2020 Collection Types in PL/SQL
© William Robertson 2007
Subscribe to articles Subscribe to code and scripts
https://www.williamrobertson.net/documents/collection-types.html 10/10
7/8/2020 Collection Types in PL/SQL
business rule. Otherwise they are just a functionally crippled version of nested table
collections, with a LIMIT clause you don't need.
Further reading
Collection extensions in 10g - oracle-developer.net
Using PL/SQL Collections and Records - Oracle 12c PL/SQL Language Reference
PL/SQL Collections and Records - Oracle 12c Object-Relational Developer's Guide
The Oracle 12c Object-Relational Developer's Guide in general
ALSO ON WILLIAMROBERTSON.NET
William Robertson code: Para
How to install Oracle William Robertson Setting up PL/SQL S
10g on Apple Mac … code: Parallel PL Developer, part 1 o
4 years ago • 2 comments 4 years ago • 6 comments 5 years ago • 12 comments 7
Oracle installation guide for Parallel PL/SQL launcher Setting up PL/SQL O
Apple Mac This is an experimental Developer gu
package for submitting …
0 Comments williamrobertson.net 🔒 Disqus' Privacy Policy
1 Login
Recommend t Tweet f Share Sort by Newest
Start the discussion…
LOG IN WITH
OR SIGN UP WITH DISQUS ?
Name
Be the first to comment.
✉ Subscribe d Add Disqus to your siteAdd DisqusAdd ⚠ Do Not Sell My Data
https://www.williamrobertson.net/documents/collection-types.html 9/10
