Oracle Database Collections

Types of Collections:

1. PL/SQL Tables or Index By Table or Associative Array
2. VARRAY
3. Nested Tables

Associative Array:

This can only exist in PL/SQL and cannot be created in SQL.

TYPE associative_array_type IS TABLE OF element_type INDEX BY key_data_type

key_data_type - PLS_INTEGER or BINARY_INTEGER or VARCHAR2(size)

v_associative_array associative_array_type;

v_associative_array(index) := value;

Key points to keep in mind before and while using Associative Arrays:

• Unbounded - No upper boundary on the number of entries that can be stored
• They are sparse - index values not necessary to be in sequence.  So there can be gaps between index values
• Cannot be persisted
• No initialization required
• Single dimensional

VARRAY:

This can be created in both SQL and PL/SQL.

--PL/SQL creation command

TYPE varray_type IS VARRAY(size) OF element_type;

--SQL creation command

CREATE [OR REPLACE] TYPE varray_type AS VARRAY(size) OF element_type;

v_varray varray_type;

--Initialization is required

v_varray := varray_type();

--Once initialized, make room for adding element(s)

v_varray.extend(n);

--Now, assign

v_varray(index) := value;

Key points to keep in mind before and while using VARRAY:

• Bounded - Upper boundary is required - you should know the size of the collection at the time of declaration
• They are dense - index values are in sequence.  So no gaps between index values
• Can be persisted
• Initialization required
• Single dimensional

Nested Table:

This can be created in both SQL and PL/SQL.

--PL/SQL creation command

TYPE nested_table_type IS TABLE OF element_type;

--SQL creation command

CREATE [OR REPLACE] TYPE nested_table_type AS TABLE OF element_type;

v_nested_table nested_table_type;

--Initialization is required

v_nested_table := nested_table_type();

--Once initialized, make room for adding element(s)

v_nested_table.extend(n);

--Now, assign

v_nested_table(index) := value;

Key points to keep in mind before and while using Nested Table:

• Unbounded
• They are dense to begin with, but can become sparse with deletion of elements
• Can be persisted
• Initialization required
• Single dimensional
• Elements are not stored in any particular order

PLS_INTEGER and BINARY_INTEGER

There is no difference between these 2 types in and after Oracle Database Version 10g.

But prior to 10g these types were not exactly the same:

PLS_INTEGER uses machine arithmetic, unlike the library arithmetic usage of NUMBER or BINARY_INTEGER data types.

Prior to 10g though only BINARY_INTEGER was allowed in Index-By (Associative array) tables.

Machine arithmetic is always faster than the Library Arithmetic:
Reason being that for any mathematical operation machine arithmetic will go to the hardware to get an answer while Library arithmetic will go to a software library to get the results.  Software library is generally slower since it emulates the functionality of the hardware.

For this reason, PLS_INTEGER was the preferred option in versions prior to 10g.

Some additional information on NUMBER, INTEGER, PLS_INTEGER and BINARY_INTEGER:

INTEGER is defined in STANDARD package as a subtype of NUMBER data type.
BINARY_INTEGER is subtype of INTEGER.
PLS_INTEGER is subtype of BINARY_INTEGER.

INTEGER and NUMBER are platform independent.

One point to keep in mind while using the PLS_INTEGER is that in case of overflow PL/SQL engine will throw an exception while in case of BINARY_INTEGER it will not throw any exception if the result is being assigned to a NUMBER data type.

Oracle PL SQL Exception Handling

Exception Types:

• Internal or System Exceptions
• User Defined Exceptions

Declaring & Raising Exceptions:

DECLARE
   invalid_format EXCEPTION;
BEGIN
   ......................
   ..................
   IF.....THEN
      RAISE invalid_format;
   END IF;
   .....................
EXCEPTION
WHEN invalid_format THEN
   ...............
END;

Pragma EXCEPTION_INIT:
This is for assigning exception names to Oracle error/exception codes that do not have predefined names. Normally we see these kind of exceptions being handled using OTHERS block, but assigning names using Pragma is more cleaner approach. Pragma is evaluated during compile time.

This assignment of name to error code is done in the declaration section:

DECLARE
   PRAGMA EXCEPTION_INIT(network_error, -12541);
BEGIN
   ......................................
EXCEPTION
WHEN network_error THEN
   .............
END;

RAISE_APPLICATION_ERROR:
This lets you raise user defined ORA- exceptions. This helps in handling business logic exceptions in a more cleaner way than returning unhandled exceptions to the caller.

RAISE_APPLICATION_ERROR(
<error number from -20000 to -20999>,
<error message>,
<TRUE/FALSE - TRUE to keep the maintain the entire error stack, default is FALSE - this will only have the latest error>]);

DECLARE
BEGIN
   IF....THEN
      RAISE_APPLICATION_ERROR(-20001, "Logical Error");
   END IF;
EXCEPTION
END;

To capture the name of the unit/procedure/function from where the error is being raised - you can use $$PLSQL_UNIT (from 10g onwards).  And to capture the line where you are logging the error you can use $$PLQSL_LINE (also from 10g onwards).

Some other useful Oracle constructs that can be used to handle and capture the exceptions effectively:

DBMS_UTILITY.FORMAT_ERROR_STACK
   This returns the formatted error stack

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
   This displays the call stack where the exceptions were raised along with the line numbers

DBMS_UTILITY.FORMAT_CALL_STACK
   This displays the formatted call stack

Oracle PL SQL Pre-Defined Exceptions

I am just listing down some of the frequently faced Oracle PL SQL Internal/System Defined Exceptions:

Exception	Raised when ...
COLLECTION_IS_NULL	Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN	Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop.
INVALID_CURSOR	Your program attempts an illegal cursor operation such as closing an unopened cursor.
INVALID_NUMBER	In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
NO_DATA_FOUND	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.
SUBSCRIPT_BEYOND_COUNT	Your program references a nested table or varray element using an index number larger than the number of elements in the collection.
TOO_MANY_ROWS	A SELECT INTO statement returns more than one row.
VALUE_ERROR	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)

References:

http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm