PL/SQL : Collections, Associative arrays, Nested tables, Varray & Collection methods

Nested tables

Nested tables are a persistent form of collections which can be created in the database as well as PL/SQL. It is an unbounded collection where the index or subscript is implicitly maintained by the Oracle server during data retrieval. Oracle automatically marks the minimum subscript as 1 and relatively handles others. As there is no upper limit defined for a nested table, its size can grow dynamically. Though not an index-value pair structure, a nested table can be accessed like an array in a PL/SQL block.

A nested table is initially a dense collection but it might become sparse due to delete operations on the collection cells.

Dense collection is the one which is tightly populated. That means, there exists no empty cells between the lower and upper indexes of the collection. Sparse collections can have empty cells between the first and the last cell of the collection. A dense collection may get sparse by performing the “delete” operations.

When a nested table is declared in a PL/SQL program, they behave as a one-dimensional array without any index type or upper limit specification.

A nested table defined in a database exists as a valid schema object type. It can be either used in a PL/SQL block to declare a PL/SQL variable for temporarily holding program data or a database column of particular nested table type can be included in a table, which can persistently store the data in the database. A nested table type column in a table resembles a table within a table, but Oracle draws an out- of-line storage table to hold the nested table data. This scenario is illustrated in the following diagram:

Whenever a database column of nested table type is created in a table (referred to as parent table), Oracle creates a storage table with the same storage options as that of the parent table. The storage table created by Oracle in the same segment carries the name as specified in the NESTED TABLE STORE AS clause during creation of the parent table. Whenever a row is created in the parent table, the following actions are performed by the Oracle server:

  • A unique identifier is generated to distinguish the nested table instances of different parent rows, for the parent row
  • The instance of the nested table is created in the storage table alongside the unique identifier of the parent row

The Oracle server takes care of these nested table operations. For the programmer or user, the whole process is hidden and appears as a normal “insert” operation.

A nested table definition in PL/SQL follows the following syntax:

DECLARE TYPE type_name IS TABLE OF element_type [NOT NULL];

In the preceding syntax, element_type is a primitive data type or a user-defined type, but not as a REF CURSOR type.

In a database, a nested table can be defined using the following syntax:

CREATE [OR REPLACE] TYPE type_name IS TABLE OF [element_type] [NOT NULL]; /

In the preceding syntax, [element_type] can be a SQL supported scalar data type, a database object type, or a REF object type. Unsupported element types are BOOLEAN, LONG, LONG-RAW, NATURAL,NATURALN, POSITIVE, POSITIVEN, REF CURSOR, SIGNTYPE, STRING, PLS_INTEGER,SIMPLE_INTEGER, BINARY_INTEGER and all other non-SQL supported data types.

If the size of the element type of a database collection type has to be increased, follow this syntax:

ALTER TYPE [type name] MODIFY ELEMENT TYPE [modified element type] [CASCADE | INVALIDATE];

The keywords, CASCADE or INVALIDATE, decide whether the collection modification has to invalidate the dependents or the changes that have to be cascaded across the dependents.

The nested table from the database can be dropped using the DROP command, as shown in the following syntax (note that the FORCE keyword drops the type irrespective of its dependents):

DROP TYPE [collection name] [FORCE]

Nested table collection type as the database object

We will go through the following illustration to understand the behavior of a nested table, when created as a database collection type:

/*Create the nested table in the database*/
SQL> CREATE TYPE NUM_NEST_T AS TABLE OF NUMBER;
/

Type created.

The nested table type, NUM_NEST_T, is now created in the database. Its metadata information can be queried from the USER_TYPES and USER_COLL_TYPES dictionary views:

SELECT type_name, typecode, type_oid
FROM USER_TYPES
WHERE type_name = 'NUM_NEST_T';

TYPE_NAME       TYPECODE        TYPE_OID
--------------- --------------- --------------------------------
NUM_NEST_T      COLLECTION      96DE421E47114638A9F5617CE735731A

Note that the TYPECODE value shows the type of the object in the database and differentiates collection types from user-defined object types:

SELECT type_name, coll_type, elem_type_name
FROM user_coll_types
WHERE type_name = 'NUM_NEST_T';

TYPE_NAME       COLL_TYPE  ELEM_TYPE_NAME
--------------- ---------- --------------------
NUM_NEST_T      TABLE      NUMBER

Once the collection type has been successfully created in the database, it can be used to specify the type for a database column in a table. The CREATE TABLE statement in the following code snippet declares a column of the NUM_NEST_T nested table type in the parent table, TAB_USE_NT_COL. The NESTED TABLE [Column] STORE AS [Storage table] clause specifies the storage table for the nested table type column. A separate table for the nested table column, NUM, ensures its out-of-line storage.

SQL> CREATE TABLE TAB_USE_NT_COL
     (ID NUMBER,
      NUM NUM_NEST_T)
          NESTED TABLE NUM STORE AS NESTED_NUM_ID;

Table created.

A nested table collection type in PL/SQL

n PL/SQL, a nested table can be declared and defined in the declaration section of the block as a local collection type. As a nested table follows object orientation, the PL/SQL variable of the nested table type has to be necessarily initialized. The Oracle server raises the exception ORA-06531: Reference to uninitialized collection if an uninitialized nested table type variable is encountered during block execution.

As the nested table collection type has been declared within the PL/SQL block, its scope, visibility, and life is the execution of the PL/SQL block only.

The following PL/SQL block declares a nested table. Observe the scope and visibility of the collection variable. Note that the COUNT method has been used to display the array elements.

/*Enable the SERVEROUTPUT to display the results*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare a local nested table collection type*/
  TYPE LOC_NUM_NEST_T IS TABLE OF NUMBER;
  L_LOCAL_NT LOC_NUM_NEST_T := LOC_NUM_NEST_T (10,20,30);
BEGIN
/*Use FOR loop to parse the array and print the elements*/
  FOR I IN 1..L_LOCAL_NT.COUNT
  LOOP
     DBMS_OUTPUT.PUT_LINE('Printing '||i||' element: '||L_LOCAL_
NT(I));
  END LOOP;
END;
/

Printing 1 element: 10
Printing 2 element: 20
Printing 3 element: 30

PL/SQL procedure successfully completed.

Additional features of a nested table

In the earlier sections, we saw the operational methodology of a nested table. We will now focus on the nested table’s metadata. Furthermore, we will demonstrate a peculiar behavior of the nested table for the “delete” operations.

Oracle’s USER_NESTED_TABLES and USER_NESTED_TABLE_COLS data dictionary views maintain the relationship information of the parent and the nested tables. These dictionary views are populated only when a database of a nested table collection type is included in a table.

The USER_NESTED_TABLES static view maintains the information about the mapping of a nested table collection type with its parent table.

The structure of the dictionary view is as follows:

SQL> desc USER_NESTED_TABLES

  Name                    Null?    Type
  ----------------------- -------- ---------------
  TABLE_NAME                       VARCHAR2(30)
  TABLE_TYPE_OWNER                 VARCHAR2(30)
  TABLE_TYPE_NAME                  VARCHAR2(30)
  PARENT_TABLE_NAME                VARCHAR2(30)
  PARENT_TABLE_COLUMN              VARCHAR2(4000)
  STORAGE_SPEC                     VARCHAR2(30)
  RETURN_TYPE                      VARCHAR2(20)
  ELEMENT_SUBSTITUTABLE            VARCHAR2(25)

Let us query the nested table relationship properties for the TAB_USE_NT_COL table from the preceding view:

SELECT parent_table_column, table_name, return_type, storage_spec
FROM user_nested_tables
WHERE parent_table_name='TAB_USE_NT_COL'
/

PARENT_TAB    TABLE_NAME       RETURN_TYPE          STORAGE_SPEC
----------------------------------------------------------------------
NUM           NESTED_NUM_ID     VALUE                DEFAULT

In the preceding view query, RETURN_TYPE specifies the return type of the collection. It can be VALUE (in this case) or LOCATOR. Another column, STORAGE_SPEC, signifies the storage scheme used for the storage of a nested table which can be either USER_SPECIFIED or DEFAULT (in this case).

The USER_NESTED_TABLE_COLS view maintains the information about the collection attributes contained in the nested tables:

SQL> desc USER_NESTED_TABLE_COLS

  Name                    Null?       Type
  ----------------------- -------- ---------------
  TABLE_NAME              NOT NULL    VARCHAR2(30)
  COLUMN_NAME             NOT NULL    VARCHAR2(30)
  DATA_TYPE                           VARCHAR2(106)
  DATA_TYPE_MOD                       VARCHAR2(3)
  DATA_TYPE_OWNER                     VARCHAR2(30)
  DATA_LENGTH             NOT NULL    NUMBER
  DATA_PRECISION                      NUMBER
  DATA_SCALE                          NUMBER
  NULLABLE                            VARCHAR2(1)
  COLUMN_ID                           NUMBER
  DEFAULT_LENGTH                      NUMBER
  DATA_DEFAULT                        LONG
  NUM_DISTINCT                        NUMBER
  LOW_VALUE                           RAW(32)
  HIGH_VALUE                          RAW(32)
  DENSITY                             NUMBER
  NUM_NULLS                           NUMBER
  NUM_BUCKETS                         NUMBER
  LAST_ANALYZED                       DATE
  SAMPLE_SIZE                         NUMBER
  CHARACTER_SET_NAME                  VARCHAR2(44)
  CHAR_COL_DECL_LENGTH                NUMBER
  GLOBAL_STATS                        VARCHAR2(3)
  USER_STATS                          VARCHAR2(3)
  AVG_COL_LEN                         NUMBER
  CHAR_LENGTH                         NUMBER
  CHAR_USED                           VARCHAR2(1)
  V80_FMT_IMAGE                       VARCHAR2(3)
  DATA_UPGRADED                       VARCHAR2(3)
  HIDDEN_COLUMN                       VARCHAR2(3)
  VIRTUAL_COLUMN                      VARCHAR2(3)
  SEGMENT_COLUMN_ID                   NUMBER
  INTERNAL_COLUMN_ID      NOT NULL    NUMBER
  HISTOGRAM                           VARCHAR2(15)
  QUALIFIED_COL_NAME                  VARCHAR2(4000)

We will now query the nested storage table in the preceding dictionary view to list all its attributes:

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, HIDDEN_COLUMN
FROM user_nested_table_cols
where table_name='NESTED_NUM_ID'
/

COLUMN_NAME                    DATA_TYP   DATA_LENGTH   HID
------------------------------ ---------- ----------- -----
NESTED_TABLE_ID                  RAW            16      YES
COLUMN_VALUE                     NUMBER         22      NO

We observe that though the nested table had only number elements, there is two- columned information in the view. The COLUMN_VALUE attribute is the default pseudo column of the nested table as there are no “named” attributes in the collection structure. The other attribute, NESTED_TABLE_ID, is a hidden unique 16-byte system generated raw hash code which latently stores the parent row identifier alongside the nested table instance to distinguish the parent row association.

If an element is deleted from the nested table, it is rendered as parse. This implies that once an index is deleted from the collection structure, the collection doesn’t restructure itself by shifting the cells in a forward direction. Let us check out the sparse behavior in the following example.

The following PL/SQL block declares a local nested table and initializes it with a constructor. We will delete the first element and print it again. The system raises the NO_DATA_FOUND exception when we query the element at the index 1 in the collection:

/*Enable the SERVEROUTPUT to display the block messages*/
SQL> SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
SQL> DECLARE

     /*Declare the local nested table collection*/
     TYPE coll_method_demo_t IS TABLE OF NUMBER;

     /*Declare a collection variable and initialize it*/
     L_ARRAY coll_method_demo_t := coll_method_demo_t
(10,20,30,40,50);
     BEGIN

     /*Display element at index 1*/
      DBMS_OUTPUT.PUT_LINE('Element at index 1 before deletion:'||l_
array(1));
     /*Delete the 1st element from the collection*/
       L_ARRAY.DELETE(1);
     /*Display element at index 1*/
       DBMS_OUTPUT.PUT_LINE('Element at index 1 after deletion:'||l_
array(1));
     END;
     /

Element at index 1 before deletion:10
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 15
The following two tabs change content below.
Saurabh Gupta
Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of the book titled "Oracle Advanced PL/SQL Developer Professional Guide" in the year 2012. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (@SAURABHKG) or through his technical blog, with comments, suggestions, and feedback regarding this book.
Saurabh Gupta

Saurabh Gupta

Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of the book titled "Oracle Advanced PL/SQL Developer Professional Guide" in the year 2012. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (@SAURABHKG) or through his technical blog, with comments, suggestions, and feedback regarding this book.

  • Vijay

    Well done…, keep it up…,, awesome

  • raj

    nice article on collections.. thanks for giving information..