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.
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.
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 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:
- The documentation can be confusing, and I think it makes the wrong assumptions about what you might be looking for.
- 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 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:
- 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 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:
- 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 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|
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,
The same thing as PLS_INTEGER, but with a slightly longer name.
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 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).
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:
- Arrays in other languages become varrays in PL/SQL.
- Sets and bags in other languages become nested tables in PL/SQL.
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; 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.
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.