PL/SQL: Using Collections

Exclusive offer: get 50% off this eBook here
Oracle Advanced PL/SQL Developer Professional Guide

Oracle Advanced PL/SQL Developer Professional Guide — Save 50%

Master advanced PL/SQL concepts along with plenty of example questions for 1Z0-146 examination with this book and ebook

$35.99    $18.00
by Saurabh K. Gupta | May 2012 | Enterprise Articles Oracle

The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts. Besides the clear and precise explanation on advanced topics, it also contains example code and demonstrations, which gives a sense of application and usage to readers

In this article by Saurabh Gupta, author of Oracle Advanced PL/SQL Developer Professional Guide, we'll cover the following topics:

  • Collections—an overview
  • Associative arrays
  • Nested tables
  • Varray
  • PL/SQL collection methods

Collections—an overview

A collection is a homogeneous single dimensional structure, which constitutes an ordered set of elements of a similar type. Being a homogeneous structure, all elements are of the same data type. The structure of the element contains cells with a subscript. The elements reside in these cells to make the index as their location information. The subscript or cell index becomes identification of an element and is used for its access.

Structure of a collection type, SPORT, is shown in the following diagram. Note the subscript and elements into it. A new element, GOLF, enters at the last empty location and is represented as SPORT [6]:

A collection element can be of any valid SQL data type or a user-defined type. An element of the SQL primitive data type is a scalar value while an element of the user-defined type is an object type instance. A collection can be used within a PL/SQL program by declaring a PL/SQL variable of collection type. The local PL/SQL variable can hold the instances of its collection type. Besides, a database column in a table can also be of the schema collection type.

The collections in Oracle are strictly one dimensional. They cannot be realized on two-dimensional coordinates. However, multidimensional arrays can be realized when the collection has an object type or collection type attribute.

A collection can be bounded or unbounded. Bounded collections can accommodate a limited number of elements while unbounded collections have no upper limit for subscripts.

Collections provide an efficient way to organize the data in an array or set format while making the use of object-oriented features. An instance of a nested table or varray collection type is accessed as an object while the data is still stored in database columns. Collections can be used to avail data caching in programs and boost up the performance of SQL operations. On dedicated server connections, a session always uses User Global Area (UGA), a component of PGA, for collection operations. On the other hand, for shared server mode, the collection operations are still carried out in UGA; but UGA is now a part of System Global Area (SGA), thus indirectly in SGA. This is because in shared server connections, multiple server processes can affect a session, thus UGA must be allocated out of the SGA.

Categorization

Collections are of two types—persistent and non-persistent. A collection is persistent if it stores the collection structure and elements physically in the database. Contrarily, a non-persistent collection is active for a program only that is, maximum up to a session.

Apart from the preceding categories, a collection can be realized in three formats namely, associative array, nested table or varray. This categorization is purely based on their objective and behavioral properties in a PL/SQL program. The following diagram combines the abstract and physical classification of collections:

We will take a quick tour of these collection types now and discuss them in detail in the coming sections:

  • Associative array (index-by table): This is the simplest form of non- persistent unbounded collections. As a non-persistent collection, it cannot be stored in the database, but they are available within a PL/SQL block only. The collection structure and data of associative array cannot be retained once the program is completed. Initially, during the days of Oracle 7, it was known as PL/SQL tables. Later, Oracle 8 version released it as index-by tables as they used an index to identify an element.
  • Nested table: This is a persistent form of unbounded collections which can be created in the database as well as in PL/SQL block.
  • Varray (variable-size array): This is a persistent but bounded form of collection which can be created in the database as well as in PL/SQL. Similar to a nested table, a varray is also a unidimensional homogeneous collection. The collection size and storage scheme are the factors which differentiate varrays from nested tables. Unlike a nested table, a varray can accommodate only a defined (fixed) number of elements.

Selecting an appropriate collection type

Here are a few guidelines to decide upon the appropriate usage of collection types in programs:

Use of associative arrays is required when:

  • You have to temporarily cache the program data in an array format for lookup purpose.
  • You need string subscripts for the collection elements. Note that it supports negative subscripts, too.
  • Map hash tables from the client to the database.

Use of nested tables is preferred when:

  • You have to stores data as sets in the database. Database columns of nested table type can be declared to hold the data persistently.
  • Perform major array operations such as insertion and deletion, on a large volume of data.

Use of varrays is preferred when:

  • You have to store calculated or predefined volume of data in the database. Varray offers limited and defined storage of rows in a collection.
  • Order of the elements has to be preserved.

Associative arrays

Associative arrays are analogous to conventional arrays or lists which can be defined within a PL/SQL program only. Neither the array structure nor the data can be stored in the database. It can hold the elements of a similar type in a key-value structure without any upper bound to the array. Each cell of the array is distinguished by its subscript, index, or cell number. The index can be a number or a string.

Associative arrays were first introduced in Oracle 7 release as PL/SQL tables to signify its usage within the scope of a PL/SQL block. Oracle 8 release identified the PL/SQL table as Index by table due to its structure as an index-value pair. Oracle 10g release recognized the behavior of index by tables as arrays so as to rename it as associative arrays due to association of an index with an array.

The following diagram explains the physical lookup structure of an associative array:

Associative arrays follow the following syntax for declaration in a PL/SQL declare block:

TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL INDEX BY [INDEX DATA TYPE]

In the preceding syntax, the index type signifies the data type of the array subscript. RAW, NUMBER, LONG-RAW, ROWID, and CHAR are the unsupported index data types. The suited index types are BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL, SIGNTYPE, or VARCHAR2.

The element's data type can be one of the following:

  • PL/SQL scalar data type: NUMBER (along with its subtypes), VARCHAR2 (and its subtypes), DATE, BLOB, CLOB, or BOOLEAN
  • Inferred data: The data type inherited from a table column, cursor expression or predefined package variable
  • User-defined type: A user defined object type or collection type

For illustration, the following are the valid conditions of the associative array in a PL/SQL block:

/*Array of CLOB data*/ TYPE clob_t IS TABLE OF CLOB INDEX BY PLS_INTEGER; /*Array of employee ids indexed by the employee names*/ TYPE empno_t IS TABLE OF employees.empno%TYPE NOT NULL INDEX BY employees.ename%type;

The following PL/SQL program declares an associative array type in a PL/ SQL block. Note that the subscript of the array is of a string type and it stores the number of days in a quarter. This code demonstrates the declaration of an array and assignment of the element in each cell and printing them. Note that the program uses the FIRST and NEXT collection methods to display the array elements. The collection methods would be covered in detail in the PL/SQL collection methods section:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare a collection type associative array and its variable*/ TYPE string_asc_arr_t IS TABLE OF NUMBER INDEX BY VARCHAR2(10); l_str string_asc_arr_t; l_idx VARCHAR2(50); BEGIN /*Assign the total count of days in each quarter against each cell*/ l_str ('JAN-MAR') := 90; l_str ('APR-JUN') := 91; l_str ('JUL-SEP') := 92; l_str ('OCT-DEC') := 93; l_idx := l_str.FIRST; WHILE (l_idx IS NOT NULL) LOOP DBMS_OUTPUT.PUT_LINE('Value at index '||l_idx||' is '||l_str(l_ idx)); l_idx := l_str.NEXT(l_idx); END LOOP; END; / Value at index APR-JUN is 91 Value at index JAN-MAR is 90 Value at index JUL-SEP is 92 Value at index OCT-DEC is 93 PL/SQL procedure successfully completed.

In the preceding block, note the string indexed array. A string indexed array considerably improves the performance by using indexed organization of array values. In the last block, we noticed the explicit assignment of data.

In the following program, we will try to populate the array automatically in the program. The following PL/SQL block declares an associative array to hold the ASCII values of number 1 to 100:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL Block*/ DECLARE /*Declare an array of string indexed by numeric subscripts*/ TYPE ASCII_VALUE_T IS TABLE OF VARCHAR2(12) INDEX BY PLS_INTEGER; L_GET_ASCII ASCII_VALUE_T; BEGIN /*Insert the values through a FOR loop*/ FOR I IN 1..100 LOOP L_GET_ASCII(I) := ASCII(I); END LOOP; /*Display the values randomly*/ DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(5)); DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(15)); DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(75)); END; / 53 49 55 PL/SQL procedure successfully completed.

The salient features of associative arrays are as follows:

  • An associative array can exist as a sparse or empty collection
  • Being a non-persistent collection, it cannot participate in DML transactions
  • It can be passed as arguments to other local subprograms within the same block
  • Sorting of an associative array depends on the NLS_SORT parameter
  • An associative array declared in package specification behaves as a session-persistent array

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

Oracle Advanced PL/SQL Developer Professional Guide Master advanced PL/SQL concepts along with plenty of example questions for 1Z0-146 examination with this book and ebook
Published: May 2012
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

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 ALTER TYPE syntax:

ALTER TYPE [varray name] MODIFY LIMIT [new size_limit] [INVALIDATE | CASCADE];

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]

Varray in PL/SQL

Similar to the handling of a nested table as PL/SQL construct, varrays also can be declared local to a PL/SQL block. In the following illustrations, observe the scope and visibility of the varray variables.

Similar to nested tables, varrays too follow object orientation. For this reason, varrays require initialization mandatorily before accessing them in the executable section of the PL/SQL block.

/*Enable the SERVEROUTPUT to display the results*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare a local varray type, define collection variable and initialize it*/ TYPE V_COLL_DEMO IS VARRAY(4) OF VARCHAR2(100); L_LOCAL_COLL V_COLL_DEMO := V_COLL_DEMO('Oracle 9i', 'Oracle 10g', 'Oracle 11g'); BEGIN /*Use FOR loop to parse the array variable and print the elements*/ FOR I IN 1..L_LOCAL_COLL.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Printing Oracle version:' ||L_LOCAL_ COLL(I)); END LOOP; END; / Printing Oracle version:Oracle 9i Printing Oracle version:Oracle 10g Printing Oracle version:Oracle 11g PL/SQL procedure successfully completed.

Varray as a database collection type

Let us illustrate the creation of a varray as a database collection type. We will see the SELECT and DML operations on varrays:

/*Create the nested table in the database*/ SQL> CREATE OR REPLACE TYPE num_varray_t AS VARRAY (5) OF NUMBER; / Type created.

Oracle maintains the complete information about the newly created varray types in the dictionary views USER_VARRAYS, USER_COLL_TYPES, and USER_TYPES. Now, we will create a table which has a column of the varray type. Note that it has no NESTED TABLE STORE AS clause as it used in the case of nested tables to specify the name of the storage table.

CREATE TABLE tab_use_va_col (ID NUMBER, NUM num_varray_t); Table created. /*Query the USER_VARRAYS to list varray information*/ SELECT parent_table_column, type_name, return_type, storage_spec FROM user_varrays WHERE parent_table_name='TAB_USE_VA_COL' / PARENT_TAB TYPE_NAME RETURN_TYPE STORAGE_SPEC ---------- --------------- -------------------- ----------------- NUM NUM_VARRAY_T VALUE DEFAULT

PL/SQL collection methods

Oracle provides a set of methods which can be used in conjunction with collections in PL/SQL blocks. These methods access a collection type variable and perform relevant activities such as extension, trimming, and deleting collection elements. Besides these activities, few methods also provide information about the collection such as COUNT and EXISTS. These utilities are known as collection methods and they are not a built-in subprogram, because they can be used exclusively in conjunction with collections.

The common syntax for all the collection methods is as follows:

[COLLECTION].METHOD (PARAMETERS)

EXISTS

The EXISTS function checks the existence of an element in a collection. The general syntax of this function is EXISTS(<index>). It takes the subscript as an input argument and searches it in the collection. If the element corresponding to the index is found, it returns TRUE or else, returns FALSE. It is the only method which doesn't raise any exception during its application with an uninitialized collection.

The following PL/SQL block declares a local nested table collection and its two variables. While one array is uninitialized, the other one is initialized with sample data. We will check the existence of the first element in both arrays:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare a local nested table collection*/ TYPE coll_method_demo_t IS TABLE OF NUMBER; /*Declare collection type variables*/ L_ARRAY1 coll_method_demo_t; L_ARRAY2 coll_method_demo_t := coll_method_demo_t (45,87,57); BEGIN /*Check if first cell exists in the array 1*/ IF L_ARRAY1.EXISTS(1) THEN DBMS_OUTPUT.PUT_LINE('Element 1 found in Array 1'); ELSE DBMS_OUTPUT.PUT_LINE('Element 1 NOT found in Array 1'); END IF; /*Check if first cell exists in the array 2*/ IF L_ARRAY2.EXISTS(1) THEN DBMS_OUTPUT.PUT_LINE('Element 1 found in Array 2'); ELSE DBMS_OUTPUT.PUT_LINE('Element 1 NOT found in Array 2'); END IF; END; / Element 1 NOT found in Array 1 Element 1 found in Array 2 PL/SQL procedure successfully completed.

COUNT

As the name suggests, the COUNT function counts the number of elements in an initialized collection. The COUNT method raises the COLLECTION_IS_NULL exception for uninitialized collections.

The COUNT function returns zero when:

  • A nested table or varray collection is initialized with an empty collection
  • An associative array doesn't have any elements

It can be operated upon all three types of collections. The following PL/SQL block declares a local nested table collection and its two variables. We will check the element count in both the collection variables:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare the local collection type*/ TYPE coll_method_demo_t IS TABLE OF NUMBER; /*Declare the collection variables and initialize them with test data*/ l_loc_var coll_method_demo_t := coll_method_demo_t (10,20,30); BEGIN DBMS_OUTPUT.PUT_LINE('The array size is '||l_loc_var.count); END; / The array size is 3 PL/SQL procedure successfully completed.

LIMIT

The LIMIT function returns the maximum number of elements that can be accommodated by a VARRAY collection type variable. This method can be used with VARRAY collection types only. The LIMIT method raises the COLLECTION_IS_NULL exception for uninitialized collections.

For associative arrays and nested tables, the LIMIT method returns NULL.

The following PL/SQL block declares a local varray type and a variable of its type. The varray type variable has been initialized with test data. Observe the difference between the COUNT and LIMIT methods:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare local varray and its variable*/ TYPE coll_method_demo_v IS VARRAY(10) OF NUMBER; L_ARRAY1 coll_method_demo_v := coll_method_demo_v (10,20,30);BEGIN /*Display the current count*/ DBMS_OUTPUT.PUT_LINE('The varray has '||L_ARRAY1.COUNT||' elements'); /*Display the maximum limit*/ DBMS_OUTPUT.PUT_LINE('The varray can hold '||L_ARRAY1.LIMIT||' elements'); END; / The varray has 3 elements The varray can hold 10 elements PL/SQL procedure successfully completed.

FIRST and LAST

The FIRST and LAST functions return the first and last subscripts of a collection. For an empty collection, these methods return NULL value. These methods can be used with all three types of collections. The FIRST and LAST methods raise exception COLLECTION_IS_NULL for uninitialized collections.

The following PL/SQL block demonstrates the use of the FIRST and LAST methods with an initialized collection:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL bock*/ DECLARE /*Display a local nested table collection*/ TYPE coll_method_demo_t IS TABLE OF NUMBER; L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30); BEGIN /*Display the first and last elements*/ DBMS_OUTPUT.PUT_LINE('First element of the array: '|| L_ARRAY (L_ ARRAY.FIRST)); DBMS_OUTPUT.PUT_LINE('Last element of the array: '|| L_ARRAY (L_ ARRAY.LAST)); END; / Starting Index of the array: 10 Last Index of the array: 30 PL/SQL procedure successfully completed.

PRIOR and NEXT

The PRIOR and NEXT functions take an input index and return its previous and next index from the given collection. If the PRIOR and NEXT functions are used with the first and last indexes respectively, the method returns NULL.

Both the methods can be used with all three types of collections. The PRIOR and NEXT methods raise exception COLLECTION_IS_NULL for uninitialized collections.

The following PL/SQL shows the usage of the PRIOR and NEXT methods with a PL/SQL type collection:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare a local nested table collection*/ TYPE coll_method_demo_t IS TABLE OF NUMBER; L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30,100,48,29,28); BEGIN /*Display the element which appears before 5th index*/ DBMS_OUTPUT.PUT_LINE('Element before 5th element: '||L_ARRAY(L_ ARRAY.PRIOR(5))); /*Display the element which appears after 6th index*/ DBMS_OUTPUT.PUT_LINE('Element after 6th element: '||L_ARRAY(L_ARRAY. NEXT(6))); END; / Element before 5th element: 100 Element after 6th element: 28 PL/SQL procedure successfully completed.

EXTEND

The EXTEND function is used to append elements to a collection variable of nested table or varray type. It cannot be used with associative arrays.

It is an overloaded function which can be used in three signatures as follows:

  • EXTEND: It appends the collection with a NULL element
  • EXTEND(x): It appends the collection with x number of NULL elements
  • EXTEND(x,y): It appends the collection with x elements and with the value as that of the y element. If the y element doesn't exist, the system raises a SUBSCRIPT_BEYOND_COUNT exception.

The following PL/SQL block demonstrates the extension using all three signatures of the EXTEND method. The first extension appends the fourth NULL element to the array. The second extension appends the fifth and sixth NULL elements to the array. The third extension appends the seventh and eighth elements as 10 (value of the first element) to the array:

/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare local nested table collection type*/ TYPE coll_method_demo_t IS TABLE OF NUMBER; /*Declare collection type variable and initialize it*/ L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30); BEGIN /*Extend the collection. It adds a NULL element to the collection*/ L_ARRAY.EXTEND; DBMS_OUTPUT.PUT_LINE(L_ARRAY.LAST||' element of the array is = '||L_ARRAY(L_ARRAY.LAST)); /*Extend the collection. It adds two NULL elements at the end of the collection*/ L_ARRAY.EXTEND(2); DBMS_OUTPUT.PUT_LINE(L_ARRAY.LAST||' element of the array is = '||L_ARRAY(L_ARRAY.LAST)); /*Extend the collection. It adds two NULL elements at the end of the collection and populates with the 1st element*/ L_ARRAY.EXTEND(2,1); DBMS_OUTPUT.PUT_LINE(L_ARRAY.LAST||' element of the array is = '||L_ARRAY(L_ARRAY.LAST)); END; / L_ARRAY(4) element of the array is = L_ARRAY(6) element of the array is = L_ARRAY(8) element of the array is = 10 PL/SQL procedure successfully completed.

The EXTEND method raises the COLLECTION_IS_NULL exception for uninitialized collections. If a varray is attempted for extension beyond its maximum allowed limit, Oracle raises a SUBSCRIPT_BEYOND_LIMIT exception.

TRIM

The TRIM function is used to cut the elements from the specified collection, of the nested table or varray type. It cannot be used with associative array type collections. TRIM is an overloaded method, which can be used in the following two signatures:

  • TRIM: It trims one element from the end of the collection
  • TRIM(n): It trims n elements from the end of the collection. If n exceeds the total count of elements in the collection, the system raises a SUBSCRIPT_ BEYOND_COUNT exception. No action has been defi ned for NULL value of n.
    • DELETE: It flushes out all the elements of a collection
    • DELETE(n): It deletes the nth index from the collection
    • DELETE(n,m): It performs range deletion, where all the elements within the range of the subscripts n and m are deleted
  • The following PL/SQL block shows the operation of the TRIM method on an initialized PL/SQL table collection type:

    /*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare a local nested table collection type*/ 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 /*Trim the last element of the collection*/ L_ARRAY.TRIM; DBMS_OUTPUT.PUT_LINE('L_ARRAY('||L_ARRAY.LAST||') element is = '||L_ARRAY(L_ARRAY.LAST)); /*Trim the last 2 elements of the collection*/ L_ARRAY.TRIM(2); DBMS_OUTPUT.PUT_LINE('L_ARRAY('||L_ARRAY.LAST||') element is = '||L_ARRAY(L_ARRAY.LAST)); END; / L_ARRAY(4) element is = 40 L_ARRAY(2) element is = 20 PL/SQL procedure successfully completed.

    Like other methods, the TRIM method raises a COLLECTION_IS_NULL exception for uninitialized collections.

    DELETE

    The DELETE function is used to delete elements from a given collection. The DELETE operation leaves the collection sparse. Any reference to the deleted index would raise a NO_DATA_FOUND exception. The DELETE method raises a COLLECTION_IS_NULL exception for uninitialized collections. It can be used with all three types of collections.

    The overloaded method can be used in the following signatures:

    The following PL/SQL block declares a coll_method_demo_t collection along with its collection variable. This program displays the fi rst element of the collection before and after the deletion of the first subscript:

    /*Enable the SERVEROUTPUT on to display the output*/ SET SERVERO UTPUT ON /*Start the PL/SQL block*/ 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 DBMS_OUTPUT.PUT_LINE('First element before deletion is :L_ ARRAY('||L_ARRAY.FIRST||') = '||L_ARRAY(L_ARRAY.FIRST)); /*Delete the 1st element from the collection*/ L_ARRAY.DELETE(1); DBMS_OUTPUT.PUT_LINE('First element after deletion is : L_ ARRAY('||L_ARRAY.FIRST||') = '||L_ARRAY(L_ARRAY.FIRST)); END; / First element before deletion is : L_ARRAY(1) = 10 First element after deletion is : L_ARRAY(2) = 20 PL/SQL procedure successfully completed.

    Interestingly, Oracle doesn't allow the deletion of individual elements in a varray collection. Either all the elements of the varray have to be removed using the VARRAY.DELETE method or the elements can be trimmed from the end of the varray collection. This scenario is illustrated in the following program:

    /*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT ON /*Start the PL/SQL block*/ DECLARE /*Declare the local varray collection*/ TYPE coll_method_demo_t IS VARRAY (10) 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 /*Delete the second element of varray*/ L_ARRAY.DELETE(2); END; / L_ARRAY.DELETE(2); * ERROR at line 8: ORA-06550: line 8, column 3: PLS-00306: wrong number or types of arguments in call to 'DELETE' ORA-06550: line 8, column 3: PL/SQL: Statement ignored

    It is recommended that the TRIM and DELETE methods must not be operated together or simultaneously on a collection. The DELETE method retains a placeholder for the deleted element, while the TRIM method destroys the element from the collection. Therefore, the operation sequence "DELETE(last) followed by TRIM(1)" would result in removal of a single element only.


Further resources on this subject:


Oracle Advanced PL/SQL Developer Professional Guide Master advanced PL/SQL concepts along with plenty of example questions for 1Z0-146 examination with this book and ebook
Published: May 2012
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Saurabh K. Gupta

Since the time Saurabh Gupta was introduced to Oracle database by chance and not by choice, he has been synchronizing his on job and off job interests with Oracle database programming. As an Oracle 11g Certified Advanced PL/SQL Professional, he soon moved from programming to database designing, development, and administration. During the phase when he specialized in database modelling, development and tuning, he was an active Oracle blogger and OTN forum member. To date, he has authored more than 70 online articles and papers. His works can be noted at RMOUG SQL> Update journal, PSOUG (http://www.psoug.org), DBANOTES (http://www.dbanotes.com), EXFORSYS (http://www.exforsys.com), and CLUB-ORACLE (http://www.club-oracle.com). He shares his technical encounters in Oracle technologies on his own blogging venture http://sbhoracle.wordpress.com/. He is an AIOUG (All India Oracle Users Group) member and loves to participate in technical meets and conferences. Besides digging into Oracle, sketching and snooker are other past times for him. One can reach him through his blog SbhOracle for any comments, suggestions or feedback regarding this book.

Books From Packt


Oracle Database 11g – Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators

Oracle SQL Developer 2.1
Oracle SQL Developer 2.1

Microsoft Office 365: Exchange Online Implementation and Migration
Microsoft Office 365: Exchange Online Implementation and Migration

Mastering SQL Queries for SAP Business One
Mastering SQL Queries for SAP Business One

PostgreSQL 9.0 High Performance
PostgreSQL 9.0 High Performance

Microsoft Forefront UAG 2010 Administrator's Handbook
Microsoft Forefront UAG 2010 Administrator's Handbook

Microsoft Dynamics Sure Step 2010
Microsoft Dynamics Sure Step 2010

Microsoft Data Protection Manager 2010
Microsoft Data Protection Manager 2010


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
F
9
y
m
V
5
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software