Collection Exceptions

The following table provides the collection exceptions(or we can say predefined exceptions) and when they are raised:

Exception	Raised when	Oracle Error
COLLECTION_IS_NULL	You try to operate on an atomically null collection.	ORA-06531
NO_DATA_FOUND	A subscript designates an element that was deleted	ORA-01403
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection	ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the legal range	ORA-06532
VALUE_ERROR	A subscript is null or not convertible to an integer	ORA-06502

 Raised in program:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
nums NumList;  -- atomically null
BEGIN
/* Assume execution continues despite the raised exceptions. */
   nums(1) := 1;            -- raises COLLECTION_IS_NULL       (1)
   nums := NumList(1,2);  -- initialize table
   nums(NULL) := 3          -- raises VALUE_ERROR              (2)
   nums(0) := 3;            -- raises SUBSCRIPT_OUTSIDE_LIMIT  (3)
   nums(3) := 3;            -- raises SUBSCRIPT_BEYOND_COUNT   (4)
   nums.DELETE(1);  -- delete element 1

   IF nums(1) = 1 THEN ...  -- raises NO_DATA_FOUND            (5)

Example:

declare
  TYPE TYPE_NAME_NUM IS TABLE OF INTEGER;
  nums TYPE_NAME_NUM; /*:=TYPE_NAME_NUM(100,200,300,400)*/;
begin
  null;
  nums(1) := 1;
exception
  when COLLECTION_IS_NULL then
    dbms_output.put_line(sqlerrm);
/*ORA-06531: Reference to uninitialized collection*/
  
end;

Associative Array(Index by Table) Vs Nested Table Vs VARRAY

What is difference between Index By Tables,Nested Tables and Varrays?

The chart below lists the properties of the Index By Tables,Nested Tables and Varrays

Index By Table	Nested Table	Varrays
Subscript Characteristics : Can be arbitrary numbers or strings. Need not be sequential.	Subscript Characteristics : Sequential numbers, starting from one.	Subscript Characteristics : Sequential numbers, starting from one.
Size : Unbounded i.e. the number of elements it can hold is not pre-defined	Size: Unbounded i.e. the number of elements it can hold is not pre-defined	Size: Bounded i.e. holds a declared number of elements, though this number can be changed at run time
Database Storage: Index by tables can be used in PL/SQL programs only, cannot be stored in the database.	Database Storage: Can be stored in the database using equivalent SQL types, and manipulated through SQL.	Database Storage: Can be stored in the database using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables)
Referencing and lookups: Works as key-value pairs.e.g. Salaries of employees can be stored with unique employee numbers used as subscripts sal(102) := 2000;	Referencing and lookups: Similar to one-column database tables. Oracle stores the nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1.	Referencing and lookups: Standard subscripting syntax e.g. color(3) is the 3rd color in varray color
Flexibility to changes: Most flexible. Size can increase/ decrease dynamically. Elements can be added to any position in the list and deleted from any position.	Flexibility to changes: Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions.	Flexibility to changes: Not very flexible. You must retrieve and update all the elements of the varray at the same time.

Collection In PL/SQL

An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value. Collections are used in some of the most important performance optimization features of PL/SQL, such as BULK COLLECT.(Collection is a group of element of same type.Each element in the list has a unique position number or label we called as "subscript".).

PL/SQL provides three collection types :

1. PL/SQL Table or Index-by tables or Associative array
2. Nested table
3. Variable-size array or Varray.

Syntax :

1. Collection Type Definition:

TYPE type_name IS 
{ assoc_array_type_def|nested_table_type_def| varray_type_def} ;

2. assoc_array_type_def:

TABLE OF element_type [ NOT NULL ]
[ INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) } ]

3. nested_table_type_def:

TABLE OF element_type [ NOT NULL ]

4. varray_type_def:

{VARRAY | VARYING ARRAY } ( size_limit )
OF element_type [ NOT NULL ]

5. element_type:

{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE
}

[element_type: The data type of the collection element (any PL/SQL data type except REF CURSOR)].

6. Collection Variable Declarations:

collection_name  type_name;

The type definition of a nested table or varray can appear either in the declarative part of a block, subprogram, package specification, or package body (in which case it is local to the block, subprogram, or package) or in the CREATE TYPE Statement (in which case it is a standalone stored type).


Here are some guidelines when we need to use which collection:

Use index by tables when:

• Your program needs small lookups.
• The collection can be made at run time in the memory when the package or procedure is initialized.
• The data volume is unknown beforehand.
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential).
• You do not need to store the collection in the database.

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change 
• when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database.
• The number of elements of the varray is known in advance.
• The data from the varray is accessed in sequence.
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray.

Check Associative Array(Index by Table) Vs Nested Table Vs VARRAY

Examples:
Check three type of collection use in program

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

Check All The Collection Methods

Check Collection Exceptions

Difference Between Collection and Record

Interview Questions