TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
the memory allocation of Varray (dense) diagrammatically.
Subscript
1
2
3
4
5
6
7
Value
Xyz
Dfv
Sde
Cxs
Vbc
Nhu
Qwe
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.
Subscript
1
2
3
4
5
6
7
Value (dense)
Xyz
Dfv
Sde
Cxs
Vbc
Nhu
Qwe
Value(sparse)
Qwe
Asd
Afg
Asd
Wer
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)
FIRST
SECOND
THIRD
FOURTH
FIFTH
SIXTH
SEVENTH
Value(sparse)
Qwe
Asd
Afg
Asd
Wer
Collection Methods
EXISTS(n)
Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT
Returns the number of elements that a collection currently contains.
LIMIT
Checks the maximum size of a collection.
FIRST
Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
LAST
Returns 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.
EXTEND
Appends 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.
TRIM
Removes one element from the end of a collection.
TRIM(n)
Removes n elements from the end of a collection.
DELETE
Removes 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_NULL
You try to operate on an atomically null collection.
NO_DATA_FOUND
A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT
A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT
A subscript is outside the allowed range.
VALUE_ERROR
A 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.