Collections in Oracle Part 2

“The process of learning requires not only hearing and applying but also forgetting and then remembering again.” John Gray. This article follows on from Collections in Oracle Part 1 Varray The syntax for defining a varray in SQL is like this: CREATE OR REPLACE TYPE varray_type AS VARRAY(SIZE) OF element_type; The syntax for defining a varray in PL/SQL is like

“The process of learning requires not only hearing and applying but also forgetting and then remembering again.”
John Gray.

This article follows on from Collections in Oracle Part 1

Varray

The syntax for defining a varray in SQL is like this:

The syntax for defining a varray in PL/SQL is like this:

After it has been defined in SQL the varray can be used as a column in the table definition, just like you would use one of the simple types.

Note that the data in the varray is store in-line, i.e. together with the rest of the data in the table, Bbt you can also use it as a variable type in PL/SQL.

For the PL/SQL compiler it makes no difference whether the varray is defined as a PL/SQL type or as an SQL object.

To add a value to the varray you have to do pretty much the same as when you are using a Nested Table. You first have to create it using the constructor.

After creation (or if the varray already existed) you have to make room for the element you want to add, by extending the varray:

Now that there is room for the element we can put the value in the varray.

Like the other two collection types, variable-sized arrays (VARRAYs) are also single-dimensional collections of homogeneous elements. However, they are always bounded and never sparse. When you define a type of VARRAY, you must also specify the maximum number of elements it can contain. Like nested tables, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved.

There are a couple of exceptions defined which you might encounter. If you try to reference a collection, but you didn’t initialize it you will get:

If you try to assign a value to an index which is bigger then the defined maximum size of the Varray, you will get:

If you try to assign a value to an index which has not been created yet, using the extend function on the Varray, you will get:

Comparison

Associative arrays are by default sparse which means that not every element between two indexes must be defined. You can for instance define an element at index 12 and the next element can be defined at index 29 with nothing in between. This opposed to Varrays which are always dense, meaning that every element between the first defined index and the last defined index must be defined with no gaps in between. Nested tables are initially dense, but they can become sparse when elements are deleted.

A lot of functions are defined on all types of collections and all have the same kind of result:

FIRST returns the index of the first element defined in the collection.

LAST returns the index of the last element defined in the collection.

NEXT(n) returns the index of the next element defined in the collection, starting at element n, skipping over gaps in the collection.

PRIOR(n) returns the index of the previous element defined in the collection, starting at element n, skipping over gaps in the collection.

COUNT returns the number of elements defined in the collection.

EXISTS(n) returns true if an element is defined at index n

DELETE[(n[,m])] removes element at index n. If index m is supplied then all the elements between n until and including m are removed. For Varrays you can just delete the entire contents of the collection.

LIMIT returns the maximum size of the collection.

EXTEND[(n[,m])] extends the collection by n elements. If m is supplied then all the new elements will get the same value as is defined at index m.

TRIM[(n)] removes n elements from the end of the collection.
Some functions are only available on the new types like LIMIT which makes no sense at all on an associative array but a Varray on the other hand has a predefined size (maximum) and it is good practice to check if there is still room for extending instead of relying on an exception to be thrown.

EXTEND is also only defined for Nested Tables and Varrays because you have to extend your collection before you can assign a value to it, which is not needed when using an Associative Array.

TRIM is also only available for Nested Tables and Varrays.

Another difference is the availability of the collection in SQL. The Associative Array is only available in PL/SQL whereas the Nested Table and the Varray are available in PL/SQL as well as in SQL.

Method     Associative Array     Nested Table     Varray     What it does    
First x x x Returns the index of the first defined element
Last x x x Returns the index of the last defined element
Next (n) x x x Returns the index of the next defined element, starting at index n
Prior (n) x x x Returns the index of the previous defined element, starting at index n
Count x x x Returns the number of defined elements in the collection
Exists (n) x x x Returns true if an element exists at index n
Delete [(n[,m])] x x x Removes elements from the collection starting at index n until and including m. For Varrays it removes the last element in the collection.
Limit (x) x Returns the maximum size of the collection. Only really useful for Varrays
Extend [(n[,m])] x x Before you can put data in the collection, you first have to extend the collection. Extend extends the colection by n elements and fills them default with the value defined at index m
Trim [(n)] x x Removes elements from the end of the collection

Ref:
www.developer.com/db/article.php/3379271/Oracle-Programming-with-PLSQL-Collections
www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-Nested-Tables
Oracle PL/SQL Programming 5th Edition – Steven Feuerstein
Oracle PL/SQL for DBA’s – Arup Nanda and Steven Feuerstein