Collections In Oracle
DEFINITION:
What are collections in Oracle?
A collection is a homogeneous single dimensional structure. It constitutes an ordered set of elements of similar type.
Since it's homogeneous structure all the elements are of same type. These types are of any valid sql datatype. Or a user-defined type. Elements constitute cells with subscript.
Collections in oracle are one dimensional. But they can be multiple dimensional as well.
Multidimensional collection can be created by creating a collection with an attribute of object type or collection type.
Collection Types: What are the types of collection in PL SQL?
| Slno | Collection Type | Features | Example |
|---|---|---|---|
| 1 | Bounded | Contains limited no of elements | Varrays |
| 2 | Unbounded | No upper limit of no of elements | Nested Table, Associative Arrays |
| 1 | Persistent | Stores collection structures and elements physically in the database | Varrays and Nested Tables |
| 2 | Non-persistent | Cannot be stored in the database and active for a program only, maximum upto a session | Associative Arrays |
| 1 | Dense | It is tightly populated. It means there is no empty cell between lower and upper indexes of the collection | Varrays, Nested Table until any deletion happened |
| 2 | Sparse | Can have empty cells between lower and upper indexes | Associative Arrays, Nested Tables after any deletion |
Collection are categories into three types:
| Collection Type | Description | Usage |
|---|---|---|
| VARRAYS | 1. This is persistent but can be used in PLSQL block as well. 2. It is purely a bounded collection 3. It is always dense 4. This collection accepts positive number subscripts only | Use Varrays when you need to store calculated or predefined volume of data |
| NESTED TABLES | 1. This is persistent but can be used in PLSQL block as well. 2. It is an unbounded collection 3. This is initially dense but later may get sparse due to deletion 4. It accept positive number subscripts only | Use Nested tables when you need to store data in the database. Also when you need to perform DML on large volume of data |
| ASSOCIATIVE ARRAYS | 1. This is purely non-persistent so can not participate in DML transactions 2. It is an unbounded collection 3. It can exists as a sparse or empty collection 4. It can be used in PLSQL block only 5. It accepts both positive or negative subscripts 6. It accepts character subscripts as well 7. Associative arrays declared in a Package specification behaves as a session persistent array. | Temporarily cache the data in an array for lookup purpose. |
Structures and Data Dictionary:
1) Nested Table: What is a nested table in Oracle?
Nested table means a table within a table. But oracle creates an out-of-line storage table to hold the nested table data.
The storage table created, carries the same name as specified in the NESTED TABLE STORE AS clause.
Whenever a row is inserted in the parent table following activities are performed by the oracle server:
a) A unique identifier is generated to distinguish the nested table instance of different parent rows with the parent row.
b) The instance of the nested table is created in the storage table. So it created along with the unique identifier of the parent row.
Data dictionary views:
Like other database objects, since nested tables are stored in DB. So there are data dictionary available for them.
- USER_NESTED_TABLES
- USER_NESTED_TABLE_COLS
2) Varrays: What is a Varrays in Oracle?
Varray follows a different storage structure than nested table. So they are stored in line with the parent record as a row value in the parent table. And the inline storage mechanism no more needs a storage clause specification, unique identifier or separate storage table.
In exception when the size of varrays exceeds 4K size, oracle follows an out-of-line storage mechanism and stores varrays as LOB.
The in-line storage mechanism of varrays helps oracle to reduce the no of IOs on the disk. This makes the varrays superior and more performance efficient than nested tables.
Data Dictionary : Support for Collection Datatypes:
In case of nested tables data dictionary is available. So same is available for Varrays. Because its also a persistent collection type.
- USER_VARRAYS
- USER_COL_TYPES
- USER_TYPES
3) Associative Arrays: What is an Associative Arrays in Oracle?
Associative is a sparse types of collections. Because the index value of associative arrays needs not to be sequential.
The index value can be any number, positive, negative, zero. It also accepts mathematical expression.
Index value must be unique.
Also we will see the index can be string as well. And its is a major difference between this collection and other two.
The Associative arrays are not stored in database. Because it exists up to a session only. So data dictionary is not available for associative arrays.
Characteristic Comparison Between Collections In Oracle:
1) We can pass persistent collection types as a formal argument to stored subprograms. But non-persistent are used in local subprograms only. Like local collection types.
2) Persistent collection types support object oriented behavior. So we will initialize them before using it. Like Nested tables and Varrays.
What is the difference between Nested Table and Associative Arrays:
| Features | Nested Table | Associative Arrays |
|---|---|---|
| Maximum Size | Dynamic | Dynamic |
| Sparsity | May exists | Exists |
| Storage | Out-of-line storage | Non-persistent temporary memory usage |
| Ordering | No retention of index ordering | Retains index order |
What is the difference between Varrays and Nested table in Oracle:
| Features | Nested Table | VArrays |
|---|---|---|
| Maximum Size | Dynamic | Fixed |
| Sparsity | May exists | Dense |
| Storage | Out-of-line storage | In line storage (upto 4K) |
| Ordering | No retention of index ordering | Retains index order |
PLSQL Exceptions – Collections In Oracle:
Collection supports exceptions. And these are different from usual exceptions available in PLSQL.
So few of common collection related exceptions are:
| Slno | Exceptions – Collections In Oracle | Description |
|---|---|---|
| 1 | COLLECTION_IS_NULL | The exception is raised when the collection is null |
| 2 | NO_DATA_FOUND | This exception is raised when the element corresponding to a subscript does not exists |
| 3 | SUBSCRIPT_BEYOND_COUNT | This exception is raised when the index exceeds the number of elements in the collection |
| 4 | SUBSCRIPT_OUTSIDE_LIMIT | This exception is raised when the index is not a legal value |
| 5 | VALUE_ERROR | This exception is raised when an element is attempted for access without index |
Methods (Built-ins) Supported By Collections In Oracle :
Data inside a collection are accessible via a list of methods. So these are very useful when we are looping through collections. Or when we are searching a data inside a collection. Also we can use these functions for doing DML against a collection.
EXISTS:
| Slno | Description |
|---|---|
| 1 | Checks the existence of an element in a collection |
| 2 | If the element corresponding to the index is found, it returns TRUE or else returns FALSE |
| 3 | It is the only method which doesn't raise any exception during its application with an uninitialized collection |
COUNT:
| Slno | Description |
|---|---|
| 1 | Returns the number of elements in an uninitialized collection |
| 2 | The COUNT method raises the COLLECTION_IS_NULL exception for uninitialized collection |
| 3 | The COUNT function returns ZERO when: 1. A Nested Table or Varray collection is initialized with an empty collection 2. An Associative Array doesn't have any element |
| 4 | It can be operated upon all three types of Collections. |
LIMIT:
| Slno | Description |
|---|---|
| 1 | Returns the maximum no of elements that can be accommodated by a Varray type of collection variable |
| 2 | This method can be used in Varray collection type only |
| 3 | For Associative Arrays and Nested Tables, LIMIT method returns NULL |
FIRST & LAST:
| Slno | Description |
|---|---|
| 1 | Returns the first and last subscripts of a collection |
| 2 | For empty collection this method returns NULL value |
| 3 | These methods can be used all three types of collections |
| 4 | FIRST and LAST methods return COLLECTION_IS_NULL exception for uninitialized collections |
PRIOR & NEXT:
| Slno | Description |
|---|---|
| 1 | The PRIOR and NEXT functions take an input index and returns its previous or next index respectively from a given collection. |
| 2 | If the PRIOR and NEXT functions are used with the first and last indexes respectively then the methods return null |
| 3 | Both the methods are used with all three types of collections |
| 4 | The PRIOR and NEXT methods raise the COLLECTION_IS_NULL exception for uninitialized collection. |
EXTEND:
| Slno | Description |
|---|---|
| 1 | Used to append elements to a collection variable of Nested Table or Varray Type |
| 2 | It cannot be used with Associative Arrays |
| 3 | Following are the calling formats: 1. EXTEND : It appends the collection with a null element 2. EXTEND(x) : It appends the collection with x number of null elements 3. EXTEND(x, y) : It appends the collection with x elements and with the value as that of the y element |
| 4 | If the y element doesn't exist, then Oracle raises a SUBSCRIPT_BEYOND_COUNT exception |
| 5 | The EXTEND method raises the COLLECTION_IS_NULL exception for uninitialized collections |
| 6 | If a Varray is attempted for extension beyond its maximum allowed limit, Oracle raises a SUBSCRIPT_BEYOND_LIMIT exception |
TRIM:
| Slno | Description |
|---|---|
| 1 | Used to cut the elements from the specified collection of Nested Table or Varray Type |
| 2 | It cannot be used with Associative Array type of collection |
| 3 | Following are the available overloading formats: 1. TRIM : It trims one element from the end of the collection 2. TRIM(n) : It trims n elements from the end of the collection |
| 4 | If n exceeds the total count of elements in the collection, oracle raises a SUBSCRIPT_BEYOND_COUNT exception. No action has been defined for NULL value of n. |
DELETE:
| Slno | Description |
|---|---|
| 1 | The DELETE operation leaves the collection as sparse |
| 2 | Any reference to the deleted index would raise NO_DATA_FOUND exception |
| 3 | The DELETE method raises a COLLECTION_IS_NULL exception for uninitialized exception |
| 4 | It can be used with all three types of collections |
| 5 | DELETE is an overloaded method and can be used with following signatures: 1. DELETE : It flushes out all the elements of a collection 2. DELETE(n) : It deletes nth index from the collection 3. DELETE(n, m) : It performs range deletion where all the elements within the range of subscripts n and m are deleted. |
Please check the Example of Collections in Oracle
RELATED TOPICS: