Exforsys

H I D E

Home arrow Technical Training arrow Oracle 11g Tutorial

Table of Contents

 Oracle 11g Collections
 Oracle 11g Collections - Nested Tables
 Oracle 11g Collections - VARRAY
 Oracle 11g Collections - Collection Methods

Oracle 11g Collections - Nested Tables

Page 2 of 4
Author: Saurabh Gupta     Published on: 2nd Jun 2011

Oracle 11g Collections

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

Sample Code
  1. DECLARE
  2. TYPE type_name IS TABLE OF element_type [NOT NULL];
Copyright exforsys.com


Element type can be a User defined type or primitive data type, but cannot be REF CURSOR.

Syntax [2b]

In SQL

Sample Code
  1. CREATE [OR REPLACE] TYPE type_name IS TABLE OF element_type;
Copyright exforsys.com


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.

Sample Code
  1. SQL> CREATE TYPE EX_NEST_TAB AS TABLE OF NUMBER;
  2. /
  3.  
  4. Type created.
Copyright exforsys.com


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]

Sample Code
  1. SQL> CREATE TABLE EX_COLL_TABLE
  2.      (COL1 NUMBER,
  3.       COL2 EX_NEST_TAB)
  4.         NESTED TABLE COL2 STORE AS NESTED_COL2;
  5.  
  6. TABLE created.
Copyright exforsys.com


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]

Sample Code
  1. SQL> SELECT TABLE_NAME, COLUMN_NAME, VIRTUAL_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME=’EX_COLL_TABLE’;
  2.  
  3. TABLE_NAME                     COLUMN_NAME                    VIR
  4. ------------------------------ ------------------------------ ---
  5. EX_COLL_TABLE                  COL1                           NO
  6. EX_COLL_TABLE                  COL2                           YES
  7. EX_COLL_TABLE                  SYS_NC0000200003$              NO
Copyright exforsys.com


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]

Sample Code
  1. SQL> INSERT INTO EX_COLL_TABLE VALUES (1, EX_NEST_TAB(100,200,300));
  2.  
  3. 1 row inserted.
  4.  
  5. SQL> INSERT INTO EX_COLL_TABLE VALUES (11, EX_NEST_TAB(123,694,212,341));
  6.  
  7. 1 row inserted.
  8.  
  9. SQL> INSERT INTO EX_COLL_TABLE VALUES (23, EX_NEST_TAB(42,20));
  10.  
  11. 1 row inserted.
  12.  
  13. SQL> COMMIT;
  14.  
  15. Commit complete.
Copyright exforsys.com


Selection: Conventional way of data selection from a table would get the data in complex and instance format.

Example [4b]

Sample Code
  1. SQL> SELECT * FROM EX_COLL_TABLE;
  2.  
  3. COL1    COL2
  4. ----    ----
  5.    1    EX_NEST_TAB(100,200,300)
  6.   11    EX_NEST_TAB(123,694,212,341)
  7.   23    EX_NEST_TAB(42,20)
Copyright exforsys.com


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]

Sample Code
  1. SQL> SELECT T1.COL1 COL1, T2.*
  2. FROM EX_COLL_TABLE T1, TABLE(T1.COL2) T2;
  3.  
  4. COL1    COLUMN_VALUE
  5. ----    ------------
  6. 1                  100 
  7. 1                  200
  8.    1       300
  9.   11               123
  10.   11               694
  11.   11               212
  12.   11               341
  13.   23                42
  14.   23                20
Copyright exforsys.com


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.

Sample Code
  1. SQL> UPDATE EX_COLL_TABLE
  2. SET COL2 = EX_NEST_TAB (93,81)
  3. WHERE COL1 = 23;
  4.  
  5. 1 row updated.
  6.  
  7. SQL> SELECT T1.COL1 COL1, T2.*
  8. FROM EX_COLL_TABLE T1, TABLE(T1.COL2) T2;
  9.  
  10. COL1    COLUMN_VALUE
  11. ----    ------------
  12. 1                  100 
  13. 1                  200
  14.    1       300
  15.   11               123
  16.   11               694
  17.   11               212
  18.   11               341
  19.   23                93
  20.   23                81
Copyright exforsys.com


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.

Sample Code
  1. SQL> CREATE OR REPLACE PROCEDURE P_CHECK_ARRAY (P_NEST EX_NEST_TAB)
  2. IS
  3. L_EVE NUMBER := 0;
  4. L_ODD NUMBER := 0;
  5. BEGIN
  6. IF P_NEST.FIRST IS NULL THEN
  7.    DBMS_OUTPUT.PUT_LINE ('Empty Collection');
  8. ELSE
  9.    FOR I IN 1..P_NEST.COUNT
  10.    LOOP
  11.         IF MOD(P_NEST(I),2) = 0 THEN
  12.            L_EVE := L_EVE+1;
  13.       ELSE  
  14.        L_ODD := L_ODD+1;
  15.         END IF;
  16.    END LOOP;
  17.    DBMS_OUTPUT.PUT_LINE ('Array contains '|| L_EVE||' even numbers');
  18.    DBMS_OUTPUT.PUT_LINE ('Array contains '|| L_ODD||' odd numbers');
  19. END IF;
  20. END;
  21. /
  22.  
  23. Procedure created.
  24.  
  25. SQL> DECLARE
  26.         L_ARRAY EX_NEST_TAB := EX_NEST_TAB();
  27.      BEGIN
  28.           P_CHECK_ARRAY (L_ARRAY);
  29.   DBMS_OUTPUT.PUT_LINE (‘----Populating array----’);
  30.   L_ARRAY := EX_NEST_TAB (24,164,256,17,82,93,52);
  31.   P_CHECK_ARRAY (L_ARRAY);
  32.      END;
  33.      /
  34.  
  35. Empty collection
  36. ----Populating array----
  37. Array contains 5 even numbers
  38. Array contains 2 odd numbers
  39.  
  40. PL/SQL procedure successfully completed.
Copyright exforsys.com


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.

Sample Code
  1. DECLARE
  2.    TYPE EX_NEST_TAB IS TABLE OF NUMBER;
  3.    L_INI_ARRAY EX_NEST_TAB := EX_NEST_TAB();
  4.    L_VAL_ARRAY EX_NEST_TAB := EX_NEST_TAB (1,2,34);
  5.    N NUMBER := 0;
  6. BEGIN
  7.    WHILE (N <10) LOOP
  8. L_INI_ARRAY.EXTEND;
  9. L_VAL_ARRAY.EXTEND;
  10. N := N+1;
  11. L_INI_ARRAY (L_INI_ARRAY.LAST) := N;
  12. L_VAL_ARRAY (L_VAL_ARRAY.LAST) := N;
  13.    END LOOP;
  14.  
  15.    DBMS_OUTPUT.PUT_LINE('----Display L_INI_ARRAY----');
  16.    FOR I IN 1..L_INI_ARRAY.COUNT
  17.    LOOP
  18. DBMS_OUTPUT.PUT_LINE('Cell: '||I||' holds element '||L_INI_ARRAY(I));
  19.    END LOOP;
  20.  
  21.    DBMS_OUTPUT.PUT_LINE('----Display L_VAL_ARRAY----');
  22.    FOR I IN 1..L_VAL_ARRAY.COUNT
  23.    LOOP
  24. DBMS_OUTPUT.PUT_LINE('Cell: '||I||' holds element '||L_VAL_ARRAY(I));
  25.    END LOOP;
  26. END;
  27. /
Copyright exforsys.com


Ads
Nested Tables Usage Notes
  1. Size of the nested table increases dynamically. It is capable of accommodating arbitrary number of elements
  2. In case of deletion of any element from a nested table, it is rendered sparse.
  3. 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.

Read Next: Oracle Functions and Procedures


 
This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial

  1. Oracle 11g Result Cache
  2. Compound Triggers in Oracle 11g
  3. Oracle 11g Invisible Index
  4. Oracle 11g Virtual Columns
  5. Oracle 11g Collections
  6. Oracle Functions and Procedures
  7. Oracle 11g Exception Handling
  8. Oracle 11g Read Only Tables
  9. Oracle 11g Subprogram Overloading
  10. Conditional compilation in Oracle PL/SQL
  11. Oracle Pragma
  12. UTL_FILE
  13. Manage Oracle dependencies
  14. Oracle 11g SecureFiles
  15. Oracle VPD implementation
  16. PL/Scope and PL/SQL Hierarchical Profiler
  17. Oracle Partitioning Overview
  18. Oracle Analytic Enhancements
  19. Oracle XML Storage
 

Comments

 

Ads

Exforsys Connect

Ads

Exforsys e-Newsletter

ebook