PL/SQL: Collections Cheat Sheet

Varrays
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

the memory allocation of Varray (dense) diagrammatically.

Subscript1234567
ValueXyzDfvSdeCxsVbcNhuQwe
Nested Tables
TYPE type_name IS TABLE OF element_type [NOT NULL]; 

table_name type_name;

The memory allocation of Nested Table (dense and sparse) diagrammatically. The black-colored element space denotes the empty element in a collection i.e. sparse.

Subscript1234567
Value (dense)XyzDfvSdeCxsVbcNhuQwe
Value(sparse)QweAsdAfgAsdWer
Index-by Table
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 

table_name type_name;

The memory allocation of Nested Table (sparse) diagrammatically. The black-colored element space denotes the empty element in a collection i.e. sparse.

Subscript (varchar)FIRSTSECONDTHIRDFOURTHFIFTHSIXTHSEVENTH
Value(sparse)QweAsdAfgAsdWer
Collection Methods
EXISTS(n)Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNTReturns the number of elements that a collection currently contains.
LIMITChecks the maximum size of a collection.
FIRSTReturns the first (smallest) index numbers in a collection that uses the integer subscripts.
LASTReturns the last (largest) index numbers in a collection that uses the integer subscripts.
PRIOR(n)Returns the index number that precedes index n in a collection.
NEXT(n)Returns the index number that succeeds index n.
EXTENDAppends one null element to a collection.
EXTEND(n)Appends n null elements to a collection.
EXTEND(n,i)Appends n copies of the ith element to a collection.
TRIMRemoves one element from the end of a collection.
TRIM(n)Removes n elements from the end of a collection.
DELETERemoves all elements from a collection, setting COUNT to 0.
DELETE(n)Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
DELETE(m,n)Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
Collection Exceptions
COLLECTION_IS_NULLYou try to operate on an atomically null collection.
NO_DATA_FOUNDA subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNTA subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMITA subscript is outside the allowed range.
VALUE_ERRORA subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

Leave a Reply

Your email address will not be published. Required fields are marked *