Collections in oracle pl sql with examples

With this article, we will know how to create and use PL/SQL collection and record variables. All these composite variables have elements that we can treat as individual variables. We can pass composite variables to subprograms as a parameter.To create a collection or record variable, first we need to define a collection or record type, and then declare a variable of that type.

The most important PLSQL collections are of 3 types as below: 1. Associative array / Index by Table 2. Nested Table 3. Varray

1. Associative Array / Index by Table :

1. Syntax:CREATE TYPE typ_nm_obj IS TABLE OF varchar2(size)/binary_integerINDEX BY varchar2(size)/binary_integer;typ_nm_tbl typ_nm_obj;2. No need to initialize.3. No need of EXTEND keyword.4. We can use both %TYPE and %ROWTYPE, we can parse records with a multiple column using Associative Array.5. The number of element is unbounded or too high.6. Size of the collection cannot increase dynamically.7. Initially, the Associative array is dense(consecutive) later it can be sparse after deletion of intermediate elements.8. An associative array cannot be stored in a table.9. we can use it only in PLSQL block.10. It cannot be an object type.

Syntax:

TYPE typee_name IS TABLE OF binary_integer/varchar2 INDEX BY varchar2/binary_integer;

2. Nested Table:

1. Syntax:CREATE TYPE typ_nm_obj IS TABLE OF varchar2(size); typ_nm_tbl typ_nm_obj := typ_nm_obj(); 2. Need to initialize, otherwise oracle will give error like: ORA-06531 Reference to uninitialized collection. 3. Need to use EXTEND keyword. 4. We can use %TYPE and %ROWTYPE, and can parse records with a multiple column using Nested Table. 5. The number of element is unbounded or too high. 6. Size of the collection can increase dynamically. 7. Initially, the Nested Table is dense(consecutive) later it can be sparse after deletion of intermediate elements. 8. Nested Table can be stored in a table. 9. we can use it both in SQL and PLSQL block. 10. It can be an object type.Syntax:TYPE type_name IS TABLE OF element_type [NOT NULL];table_name type_name;

3. VArray:

1. Syntax: CREATE TYPE typ_nm_obj IS VARRAY OF varchar2(size); typ_nm_tbl typ_nm_obj := typ_nm_obj(); 2. Need to initialize, orherwise oracle will give error like: ORA-06531 Reference to uninitialized collection. 3. Need to use EXTEND keyword. 4. We can use %TYPE and %ROWTYPE, and can parse records with multiple columns using Varray. 5. The number of element are bounded up to the provided limit. 6. Size of the collection can increase dynamically. 7. Varray is always dense(consecutive) because deletion of intermediate elements is not allowed. 8. Varray can be stored in a table. 9. we can use it both in SQL and PLSQL block. 10. It can be an object type.