Composite data types are hybrid types which are capable of holding components of varying data type. Oracle implements composite data type in two forms, either as PL/SQL records or PL/SQL collections.
Collections are one of the efficient implementations of persistent composite types in Oracle programming language. A collection is a group of elements which are similar in type and property. The tutorial covers the basics of collections, its types and collection methods.
Collections: Broad Classification
Collections can be abstractly categorized based on their ability to be stored physically as-
- Persistent collection – The collections which are physically stored in database are known as Persistent Collections. They are created as schema objects and are valid user defined types.
- Non persistent collection – The collections which are not stored in database are known as Non Persistent Collections. They are visible only within a PL/SQL block.
Collections: Behavioral Classification
Based on the behavior in a program, collections can exist in three types.
- Associative Arrays alias Index By tables. They are non persistent form of collection and maintain the index-value structure. They have sequential numeric or non numeric subscript.
- Nested Tables – Persistent form of collections, which hold index-value structure. Index is a hidden attribute and internally maintained by oracle.
- VARRAY – Similar to Nested tables with the difference that the number of element is fixed in VARRAY.
Associative arrays
Associative arrays were introduced in Oracle 7 release, with the name PL/SQL tables. Due to their structure, they were recalled as Index by tables in Oracle 8 and above releases. Owing to its similarity with the conventional arrays, Oracle 10g called it as Associative array.
Associative arrays are simplest unit of ordered set of elements in a form of an array or list which can hold any number of elements. They are physically not stored in the database; instead they are declared within a PL/SQL block and are persistent within the scope of an anonymous block execution (in which they are declared).
As stated earlier, associative array exist in index-value structure. In an associative array, an element value can be located based on the cell index (also known as subscript). The index has to be unique and can be of number or string type.
Syntax [1]
TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL INDEX BY [INDEX DATA TYPE (BINARY_INTEGER | PLS_INTEGER | VARCHAR2)]
In the above syntax, RAW, LONG RAW, ROWID, and CHAR are the unsupported index data types.
Example [1]
The Associative array EX_MONTH_TAB holds the days in each month of the first half of the year. Note the string subscript and number value.
DECLARE TYPE EX_MONTH_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10); L_ARRAY EX_MONTH_TAB; BEGIN L_ARRAY('JAN') := 31 ; L_ARRAY('FEB') := 28 ; L_ARRAY('MAR') := 31 ; L_ARRAY('APR') := 30 ; L_ARRAY('MAY') := 31 ; L_ARRAY('JUN') := 30 ; END; / PL/SQL PROCEDURE successfully completed.
Associative array usage notes:
- Associative array do not require initialization in the DECLARE section, but exists as an empty collection. This implies that associative array constant cannot exist.
- Being a non persistent composite type, associative array value cannot be modified using DML operation.
- Associative arrays can be passed as subprogram parameters or can be return type of a function.
Example [2]:
The PL/SQL block below uses a local procedure P_ARRAY to print the associative array passed as input.
DECLARE TYPE EX_MONTH_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10); L_ARRAY EX_MONTH_TAB; PROCEDURE PRINT_ARRAY (P_ARRAY EX_MONTH_TAB) IS I VARCHAR2(10); BEGIN I := P_ARRAY.FIRST; LOOP DBMS_OUTPUT.PUT_LINE('Month '||I||' has '||P_ARRAY(I)||’ days’); I := P_ARRAY.NEXT(I); EXIT WHEN I IS NULL; END LOOP; END; BEGIN L_ARRAY('JAN') := 31 ; L_ARRAY('FEB') := 28 ; L_ARRAY('MAR') := 31 ; L_ARRAY('APR') := 30 ; L_ARRAY('MAY') := 31 ; L_ARRAY('JUN') := 30 ; PRINT_ARRAY (L_ARRAY); END;
- Sort order of an associative array is based on NLS_SORT initialization parameter.
- If an associative array has to be passed to another remote database, keep note of NLS_SORT and NLS_COMP parameter values. If the values at target database are different from that of source, exceptions may occur.
- Associative array declared in Package specification behaves as Persistent collection.
- Associative array applications: Can be used as a small temporary data table, which can be reused every time the program is invoked.
Nested Tables
Nested tables are persistent form of collections which are physically stored in database (disk space consumption based on number of elements they hold). They are unbounded and distributed set of elements in key-value structure.
As per the Oracle internal architecture, a nested table type is stored as a table structure. If a column of nested table type is included in a table, oracle generates a separate relational table to hold the data and actual column referring to it. This system generated table is maintained by the server and it is named in ‘SYS_NCXXXXX$’ format. The actual table column is marked as a Virtual Column by Oracle.
During retrieval of a nested table data, the nested table rows are fetched in sequence of the subscript. Note that the subscript is not stored along with the nested table but Oracle implicitly maintains the index to identify each row in a nested table.
Syntax [2a]
In PL/SQL
DECLARE TYPE type_name IS TABLE OF element_type [NOT NULL];
Element type can be a User defined type or primitive data type, but cannot be REF CURSOR.
Syntax [2b]
In SQL
CREATE [OR REPLACE] TYPE type_name IS TABLE OF element_type;
Element types cannot be any of BOOLEAN, LONG, LONG RAW, NATURAL, NATURALN, POSITIVE, POSITIVEN, REF CURSOR, SIGNTYPE, or STRING.
Example [3a]
The example below demonstrates the creation of a table with a column of nested table type.
SQL> CREATE TYPE EX_NEST_TAB AS TABLE OF NUMBER; / TYPE created.
Note the syntax to create the table with a nested table column. The table definition must specify an arbitrary name of separate storage table, which would be used by the server to hold column data.
Example [3b]
SQL> CREATE TABLE EX_COLL_TABLE (COL1 NUMBER, COL2 EX_NEST_TAB) NESTED TABLE COL2 STORE AS NESTED_COL2; TABLE created.
Amazingly, when the column was queried in USER_TAB_COLS view, it was discovered as a virtual column in the USER_TAB_COLS dictionary view. Oracle internally identifies a nested table collection type column as a virtual column.
Example [3c]
SQL> SELECT TABLE_NAME, COLUMN_NAME, VIRTUAL_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME=’EX_COLL_TABLE’; TABLE_NAME COLUMN_NAME VIR ------------------------------ ------------------------------ --- EX_COLL_TABLE COL1 NO EX_COLL_TABLE COL2 YES EX_COLL_TABLE SYS_NC0000200003$ NO
USER_NESTED_TABLES is another dictionary view which maintains the metadata of nested tables which are owned by the current user.
The additional storage table (NESTED_COL2 in this case) is also maintained in USER_TABLES dictionary view, with NESTED column value as YES.
Handling Nested tables: Initialization, DML operations, and Selection
Nested table in SQL as standalone type stored in database: In SQL, nested table is physically stored in the database. Later it can be used in an anonymous PL/SQL block or during table creation. We shall see the DML operations on nested tables and their Selection.
Insertion: INSERT statement can be prepared using the nested table collection type.
Example [4a]
SQL> INSERT INTO EX_COLL_TABLE VALUES (1, EX_NEST_TAB(100,200,300)); 1 ROW inserted. SQL> INSERT INTO EX_COLL_TABLE VALUES (11, EX_NEST_TAB(123,694,212,341)); 1 ROW inserted. SQL> INSERT INTO EX_COLL_TABLE VALUES (23, EX_NEST_TAB(42,20)); 1 ROW inserted. SQL> COMMIT; Commit complete.
Selection: Conventional way of data selection from a table would get the data in complex and instance format.
Example [4b]
SQL> SELECT * FROM EX_COLL_TABLE; COL1 COL2 ---- ---- 1 EX_NEST_TAB(100,200,300) 11 EX_NEST_TAB(123,694,212,341) 23 EX_NEST_TAB(42,20)
To get the data in the readable format, TABLE function can be used. It brings the nested table column data in relational format, thereby making it easy inferable.
Example [4c]
SQL> SELECT T1.COL1 COL1, T2.* FROM EX_COLL_TABLE T1, TABLE(T1.COL2) T2; COL1 COLUMN_VALUE ---- ------------ 1 100 1 200 1 300 11 123 11 694 11 212 11 341 23 42 23 20
Update the collection attribute value
Example [4d]
The update statement below updates the collection column in the table. Alternatively, it can be done using a PL/SQL block too.
SQL> UPDATE EX_COLL_TABLE SET COL2 = EX_NEST_TAB (93,81) WHERE COL1 = 23; 1 ROW updated. SQL> SELECT T1.COL1 COL1, T2.* FROM EX_COLL_TABLE T1, TABLE(T1.COL2) T2; COL1 COLUMN_VALUE ---- ------------ 1 100 1 200 1 300 11 123 11 694 11 212 11 341 23 93 23 81
As a subprogram parameter: Nested table column can be passed as a parameter too. Additionally, it can be the return type of a stored function.
Example [4e]
The procedure P_CHECK_ARRAY counts of even and odd numbers in the input array. The PL/SQL block below uses the above procedure to verify the objective.
SQL> CREATE OR REPLACE PROCEDURE P_CHECK_ARRAY (P_NEST EX_NEST_TAB) IS L_EVE NUMBER := 0; L_ODD NUMBER := 0; BEGIN IF P_NEST.FIRST IS NULL THEN DBMS_OUTPUT.PUT_LINE ('Empty Collection'); ELSE FOR I IN 1..P_NEST.COUNT LOOP IF MOD(P_NEST(I),2) = 0 THEN L_EVE := L_EVE+1; ELSE L_ODD := L_ODD+1; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE ('Array contains '|| L_EVE||' even numbers'); DBMS_OUTPUT.PUT_LINE ('Array contains '|| L_ODD||' odd numbers'); END IF; END; / PROCEDURE created. SQL> DECLARE L_ARRAY EX_NEST_TAB := EX_NEST_TAB(); BEGIN P_CHECK_ARRAY (L_ARRAY); DBMS_OUTPUT.PUT_LINE (‘----Populating array----’); L_ARRAY := EX_NEST_TAB (24,164,256,17,82,93,52); P_CHECK_ARRAY (L_ARRAY); END; / Empty collection ----Populating array---- Array contains 5 even numbers Array contains 2 odd numbers PL/SQL PROCEDURE successfully completed.
Nested tables as PL/SQL construct: Initialization and usage: When defined in a PL/SQL block, nested table variable remains uninitialized and NULL collection. It is mandatory to initialize it to make it empty, a non null collection; else it raises ORA-06531 exception.
Example [5a]
A PL/SQL block declares initializes and prints two nested table collection arrays. Collection methods used in the blocks are discussed later in this tutorial.
DECLARE TYPE EX_NEST_TAB IS TABLE OF NUMBER; L_INI_ARRAY EX_NEST_TAB := EX_NEST_TAB(); L_VAL_ARRAY EX_NEST_TAB := EX_NEST_TAB (1,2,34); N NUMBER := 0; BEGIN WHILE (N <10) LOOP L_INI_ARRAY.EXTEND; L_VAL_ARRAY.EXTEND; N := N+1; L_INI_ARRAY (L_INI_ARRAY.LAST) := N; L_VAL_ARRAY (L_VAL_ARRAY.LAST) := N; END LOOP; DBMS_OUTPUT.PUT_LINE('----Display L_INI_ARRAY----'); FOR I IN 1..L_INI_ARRAY.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Cell: '||I||' holds element '||L_INI_ARRAY(I)); END LOOP; DBMS_OUTPUT.PUT_LINE('----Display L_VAL_ARRAY----'); FOR I IN 1..L_VAL_ARRAY.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Cell: '||I||' holds element '||L_VAL_ARRAY(I)); END LOOP; END; /
Nested Tables Usage Notes
- Size of the nested table increases dynamically. It is capable of accommodating arbitrary number of elements
- In case of deletion of any element from a nested table, it is rendered sparse.
- Applications of Nested tables: They fit into almost major scenarios in real time applications. Mostly, when the number of elements is variable or not known, nested tables are the best choice.
VARRAY
VARRAYs are enhanced version of nested tables, which were introduced in Oracle 8i. They contain limited and defined number of densely populated elements, whose index is sequential and numeric. The minimum bound of the index is 1, current bound is the total number of resident elements and maximum bound is the varray size. At any moment, current bound cannot exceed the maximum bound.
In terms of handling and usage, they are similar to nested tables in major situations. VARRAYs can exist as schema objects, referenced from PL/SQL blocks, and can be stored in database tables.
Unlike nested tables, which are always stored as separate storage table, varrays exist as single object in the same table. Only in exceptional cases when size of varray exceeds 4KB, it is relocated outside the table but in the same tablespace.
Syntax [3a]
In SQL
CREATE [OR REPLACE] TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type
Syntax [3b]
In PL/SQL
DECLARE TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
Size_limit represents the maximum count of elements in the array. Being a persistent form of collection, varrays require initialization.
Element_type can be one of the data type available till Oracle 11g.
VARRAYS in PL/SQL
Example [6a]
VARRAYs can be locally declared in a PL/SQL block and can be referenced within the scope of the block.
DECLARE TYPE EX_VRR_TAB IS VARRAY(5) OF VARCHAR2(100); L_BATSMAN EX_VRR_TAB := EX_VRR_TAB(); L_WICKET EX_VRR_TAB := EX_VRR_TAB(); L_BOWLER EX_VRR_TAB := EX_VRR_TAB(); BEGIN L_BATSMAN := EX_VRR_TAB(‘Sachin’,’Sehwag’,’Gambhir’,’Kohli’,’Yuvraj’,); L_WICKET := EX_VRR_TAB(‘Dhoni’,); L_BOWLER := EX_VRR_TAB(‘Bhajji’,’Nehra’,’Zaheer’,’Praveen’,’Munaf’); END;
VARRAYs in SQL
Example [6b]
The example below shows the creation of a VARRAY as schema object
SQL> CREATE OR REPLACE TYPE NUM_VARRAY AS VARRAY(10) OF INTEGER; / TYPE created.
Example [6c]
The VARRAY created in the schema can be referenced in a PL/SQL block
DECLARE L_INTEGER NUM_VARRAY := NUM_VARRAY(); BEGIN FOR I IN 1..5 LOOP L_INTEGER.EXTEND; L_INTEGER(I) := i*2; END LOOP; END;
Example [6d]
The VARRAY created can be used as a column type. The example demonstrates the Insert and Update process.
SQL> CREATE OR REPLACE TYPE SALES_TYPE AS OBJECT ( SALES_DATE DATE, SALES_QTY NUMBER); TYPE created. SQL> CREATE OR REPLACE TYPE SALES_VARRAY AS VARRAY(10) OF SALES_TYPE; TYPE created. SQL> CREATE TABLE ORDERS (ORD_ID NUMBER, ITEM_CODE VARCHAR2(100), ITEM_SALES SALES_VARRAY); TABLE created. SQL> INSERT INTO ORDERS (SEQ_ORD.NEXTVAL, ‘ELEC’, SALES_VARRAY( SALES_TYPE(SYSDATE,130), SALES_TYPE(SYSDATE-5,57), SALES_TYPE(SYSDATE-10,130) ) ); 1 ROW inserted. SQL>DECLARE L_SALES SALES_VARRAY ; BEGIN SELECT ITEM_SALES INTO L_SALES FROM ORDERS WHERE ORD_ID=100 FOR UPDATE OF ITEM_SALES; L_SALES(2).SALES_QTY := 140 ; -- update the second element UPDATE ORDERS SET ITEM_SALES = L_SALES WHERE ORD_ID=100; END ; / PL/SQL block successfully completed. SQL> COMMIT; Commit complete.
Collection methods
Oracle provides set of inbuilt methods, which can be used with a non null collection (except EXISTS), to yield some information and perform basic operations on the collection subscript and element. The list below shows the collections available and their applicability with the different collection types. If the collection is NULL, system raises COLLECTION_IS_NULL exception.
The collection information includes their null behavior of the collection, and element count. The basic utilities provided by Oracle are collection extension, deletion, and trimming.
All collection methods are compiled along with their behavior, in the below table.
Syntax [4]
collection_name.method_name[(parameters)]
These collection methods can be used as PL/SQL construct. Oracle does not support the collection methods in SQL.
Now, we shall study the usage of collection methods.
EXISTS
The method checks a collection for the NULL property. It returns FALSE for null collections while TRUE for initialized ones. It is the only collection method which is capable to work with NULL collections.
Note that if the input index value is out of range of the collection, system raises SUBSCRIPT_OUTSIDE_LIMIT exception.
Check the below illustration to demonstrate its use.
Example [7a]
IF EX_NEST_TAB.EXISTS (5) THEN ... ELSE … END IF;
COUNT
The method counts the number of elements in a collection. Check the below illustration.
Check the below illustration to demonstrate the use of COUNT method.
Example [7b]
DECLARE TYPE T IS TABLE OF NUMBER; L T := T(); BEGIN FOR I IN 1..100 LOOP L.EXTEND; L(L.LAST):=NULL; END LOOP; DBMS_OUTPUT.PUT_LINE('The Collection has '||to_char(L.COUNT)||' elements'); END; / The Collection has 100 elements PL/SQL block successfully completed.
LIMIT
The method returns the maximum count of elements in a collection array. It can only be used with VARRAYs.
Note the difference between COUNT and LIMIT methods in the below example.
Example [7c]
DECLARE TYPE v IS VARRAY(10) OF NUMBER; M V := V(); BEGIN M := V(1,3,2,3,2,3); DBMS_OUTPUT.PUT_LINE('The Collection has '||to_char(M.COUNT)||' elements'); DBMS_OUTPUT.PUT_LINE('The Collection can accommodate '||to_char(M.limit)||' elements'); END; / The Collection has 6 elements The Collection can accommodate 10 elements PL/SQL PROCEDURE successfully completed.
FIRST and LAST
The methods return the first and last subscript of a collection. For an uninitialized or empty collection, the method values are NULL. Check the below illustration to demonstrate its use of the methods.
Example [7d]
DECLARE TYPE T IS TABLE OF NUMBER; M T := T(); BEGIN DBMS_OUTPUT.PUT_LINE('FIRST and LAST subscripts for Empty collection are '||M.FIRST||' and '||M.LAST); M := T(272,4720,482,183,481,372,482,127,5943); DBMS_OUTPUT.PUT_LINE('FIRST and LAST subscripts for populated collection are '||M.FIRST||' and '||M.LAST); END; / FIRST AND LAST subscripts FOR Empty collection are AND FIRST AND LAST subscripts FOR populated collection are 1 AND 9 PL/SQL PROCEDURE successfully completed.
PRIOR and NEXT
The method give the subscript which is before and after the current subscript. These methods are used as to avoid NO_DATA_FOUND exception in sparse collections.
Example [7e]
DECLARE TYPE T IS TABLE OF NUMBER; M T := T(12,15,73,1,87,22,89); BEGIN DBMS_OUTPUT.PUT_LINE('Subscript before the 5th element is:'||TO_CHAR(M.PRIOR(5))); DBMS_OUTPUT.PUT_LINE('Subscript after the 5th element is:'||TO_CHAR(M.NEXT(5))); END; / Subscript BEFORE the 5th element IS:4 Subscript after the 5th element IS:6 PL/SQL PROCEDURE successfully completed.
EXTEND
The method allows addition of new elements to a persistent collection. By default, it appends one element to a collection. Upon specification, it can add N elements to a collection. EXTEND(m,n) make ‘m’ copies of nth element to a collection.
Note that is cannot be used for Associative arrays.
Example [7f]
DECLARE TYPE T IS TABLE OF NUMBER; M T := T(12,15,73,1,87,22,89); BEGIN DBMS_OUTPUT.PUT_LINE('No of elements in Collection:'|| TO_CHAR(M.COUNT)); M.EXTEND; DBMS_OUTPUT.PUT_LINE('No of elements in Collection after first extension:'|| TO_CHAR(M.COUNT)); M.EXTEND(5); DBMS_OUTPUT.PUT_LINE('No of elements in Collection after second extension:'|| TO_CHAR(M.COUNT)); M.EXTEND(2,3); DBMS_OUTPUT.PUT_LINE('No of elements in Collection after third extension:'|| TO_CHAR(M.COUNT)); END; / No OF elements IN Collection:7 No OF elements IN Collection after FIRST extension:8 No OF elements IN Collection after SECOND extension:13 No OF elements IN Collection after third extension:15 PL/SQL PROCEDURE successfully completed.
TRIM
As the name suggests, the method trims a persistent collection. By default, it trims single element in a collection. It can also trim N elements, if the count is specified with the method/
Note that is cannot be used for Associative arrays.
Example [7g]
DECLARE TYPE T IS TABLE OF NUMBER; M T := T(12,15,73,1,87,22,89); BEGIN DBMS_OUTPUT.PUT_LINE('Elements in Collection:'|| TO_CHAR(M.COUNT)); M.TRIM; DBMS_OUTPUT.PUT_LINE('Elements in Collection after first trim:'|| TO_CHAR(M.COUNT)); M.TRIM(5); DBMS_OUTPUT.PUT_LINE('Elements in Collection after second trim:'|| TO_CHAR(M.COUNT)); END; / Elements IN Collection:7 Elements IN Collection after FIRST TRIM:6 Elements IN Collection after SECOND TRIM:1 PL/SQL PROCEDURE successfully completed.
DELETE
The method drops a cell from a collection. It removes a single element by default, but can remove N elements and even by range, if specified with the method.
Example [7h]
DECLARE TYPE T IS TABLE OF NUMBER; M T := T(12,15,73,1,87,22,89); BEGIN DBMS_OUTPUT.PUT_LINE ('Count of elements before deletion:'||TO_CHAR(M.COUNT)); DBMS_OUTPUT.PUT_LINE ('Last index of elements before deletion:'||TO_CHAR(M.LAST)); M.DELETE(2); DBMS_OUTPUT.PUT_LINE ('Count of elements after deletion:'||TO_CHAR(M.COUNT)); DBMS_OUTPUT.PUT_LINE ('Last subscript of elements after deletion:'||TO_CHAR(M.LAST)); END; / COUNT OF elements BEFORE deletion:7 LAST INDEX OF elements BEFORE deletion:7 COUNT OF elements after deletion:6 LAST INDEX OF elements after deletion:7 PL/SQL PROCEDURE successfully completed.
