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
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, , is a hidden unique 16-byte system
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
Varray
Varrays were introduced in Oracle8i as a modified format of a nested table. The varray or variable size arrays are bounded and the persistent form of collection whose major operational features resemble nested tables. The varray declaration defines the limit of elements a varray can accommodate. 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, the current bound cannot exceed the maximum bound.
Like nested tables, varrays can be created as database objects and can also be used in PL/SQL. Though the implementation is the same as a nested table, varray follow a different storage orientation than the nested tables. They are stored in line with their parent record as a raw value in the parent table. The inline storage mechanism no more needs a storage clause specification, unique identifier or separate storage table. For some exceptional situations when the varray exceeds 4 K data, Oracle follows the out-of-line storage mechanism and stores varray as an LOB.
The inline storage mechanism of varrays helps Oracle to reduce the number of IOs on the disk. This makes varrays superior and more performance efficient than nested tables.
As a database collection type, varrays can be a valid type for a table column or object type attribute. If declared in a PL/SQL block, varrays are visible only within the block.
The syntax for varrays, when defined as a database collection type, is as follows:
CREATE [OR REPLACE] TYPE type_name IS {VARRAY | VARYING ARRAY} (size_ limit) OF element_type
In PL/SQL, varrays can be declared as follows:
DECLARE
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
In the preceding syntax, size_limit represents the maximum count of elements
in the array.
If the varray size has to be modified after its creation in the database, follow this
ALTERTYPE syntax:
The varray size can only be increased by using the ALTER TYPE... MODIFY statement. Even if the current maximum size has not been utilized, Oracle doesn't allow the ripping off a varray size. If a user attempts to reduce the varray size, Oracle raises the PLS-00728: the limit of a VARRAY can only be increased and to a maximum 2147483647 exception and invalidates the varray collection type.
The INVALIDATE and CASCADE options signify the invalidation or propagation effect
on the dependent objects as a result of the type alteration. Use the DROP command to drop a varray type from the database:
DROP TYPE [varray type name] [FORCE]