Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Collections In Oracle

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?

SlnoCollection TypeFeaturesExample
1BoundedContains limited no of elementsVarrays
2UnboundedNo upper limit of no of elementsNested Table, Associative Arrays
1PersistentStores collection structures and elements physically in the databaseVarrays and Nested Tables
2Non-persistentCannot be stored in the database and active for a program only, maximum upto a sessionAssociative Arrays
1DenseIt 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
2SparseCan have empty cells between lower and upper indexesAssociative Arrays, Nested Tables after any deletion

Collection are categories into three types:

Collections In Oracle
Collection TypeDescriptionUsage
VARRAYS1. 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 TABLES1. 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:

FeaturesNested TableAssociative Arrays
Maximum SizeDynamicDynamic
SparsityMay existsExists
StorageOut-of-line storageNon-persistent temporary memory usage
OrderingNo retention of index orderingRetains index order
Difference between Nested table and Associative Arrays Collections In Oracle

What is the difference between Varrays and Nested table in Oracle:

FeaturesNested TableVArrays
Maximum SizeDynamicFixed
SparsityMay existsDense
StorageOut-of-line storageIn line storage (upto 4K)
OrderingNo retention of index orderingRetains index order
Difference between Varrays and Nested table Collections In Oracle

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:

SlnoExceptions – Collections In OracleDescription
1COLLECTION_IS_NULLThe exception is raised when the collection is null
2NO_DATA_FOUNDThis exception is raised when the element corresponding
to a subscript does not exists
3SUBSCRIPT_BEYOND_COUNTThis exception is raised when the index exceeds the
number of elements in the collection
4SUBSCRIPT_OUTSIDE_LIMITThis exception is raised when the index is not a legal value
5VALUE_ERRORThis exception is raised when an element is attempted for access
without index
Exceptions – Collections In Oracle

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:

SlnoDescription
1Checks the existence of an element in a collection
2If the element corresponding to the index is found, it returns TRUE or else returns FALSE
3It is the only method which doesn't raise any exception during its application with an uninitialized collection
EXISTS method of Collections in Oracle

COUNT:

SlnoDescription
1Returns the number of elements in an uninitialized collection
2The COUNT method raises the COLLECTION_IS_NULL exception for uninitialized collection
3The 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
4It can be operated upon all three types of Collections.
COUNT method of Collections in Oracle

LIMIT:

SlnoDescription
1Returns the maximum no of elements that can be accommodated by a Varray type of collection variable
2This method can be used in Varray collection type only
3For Associative Arrays and Nested Tables, LIMIT method returns NULL
LIMIT method of Collections in Oracle

FIRST & LAST:

SlnoDescription
1Returns the first and last subscripts of a collection
2For empty collection this method returns NULL value
3These methods can be used all three types of collections
4FIRST and LAST methods return COLLECTION_IS_NULL exception for uninitialized collections
FIRST & LAST method of Collections in Oracle

PRIOR & NEXT:

SlnoDescription
1The PRIOR and NEXT functions take an input index and returns its previous or next index respectively from a given collection.
2If the PRIOR and NEXT functions are used with the first and last indexes respectively then the methods return null
3Both the methods are used with all three types of collections
4The PRIOR and NEXT methods raise the COLLECTION_IS_NULL exception for uninitialized collection.
PRIOR & NEXT method of Collections in Oracle

EXTEND:

SlnoDescription
1Used to append elements to a collection variable of Nested Table or Varray Type
2It cannot be used with Associative Arrays
3Following 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
4If the y element doesn't exist, then Oracle raises a SUBSCRIPT_BEYOND_COUNT exception
5The EXTEND method raises the COLLECTION_IS_NULL exception for uninitialized collections
6If a Varray is attempted for extension beyond its maximum allowed limit, Oracle raises a SUBSCRIPT_BEYOND_LIMIT exception
EXTEND method of Collections in Oracle

TRIM:

SlnoDescription
1Used to cut the elements from the specified collection of Nested Table or Varray Type
2It cannot be used with Associative Array type of collection
3Following 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
4If 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.
TRIM method of Collections in Oracle

DELETE:

SlnoDescription
1The DELETE operation leaves the collection as sparse
2Any reference to the deleted index would raise NO_DATA_FOUND exception
3The DELETE method raises a COLLECTION_IS_NULL exception for uninitialized exception
4It can be used with all three types of collections
5DELETE 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.
DELETE method of Collections in Oracle

Please check the Example of Collections in Oracle

RELATED TOPICS:

Leave a Comment

Your email address will not be published.