Associative Array (formerly called PL/SQL table or index-by table)

Declaring Associative Array Constants

When declaring an associative array constant, you must create a function that populates the associative array with its initial value and then invoke the function in the constant declaration, as in Example . (The function does for the associative array what a constructor does for a varray or nested table. For information about constructors, see "Collection Constructors".)

Example Declaring Associative Array Constant

CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS

  TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;

  FUNCTION Init_My_AA RETURN My_AA;

END My_Types;

/

CREATE OR REPLACE PACKAGE BODY My_Types IS

  FUNCTION Init_My_AA RETURN My_AA IS

    Ret My_AA;

  BEGIN

    Ret(-10) := '-ten';

    Ret(0) := 'zero';

    Ret(1) := 'one';

    Ret(2) := 'two';

    Ret(3) := 'three';

    Ret(4) := 'four';

    Ret(9) := 'nine';

    RETURN Ret;

  END Init_My_AA;

END My_Types;

/

DECLARE

  v CONSTANT My_Types.My_AA := My_Types.Init_My_AA();

BEGIN

  DECLARE

    Idx PLS_INTEGER := v.FIRST();

  BEGIN

    WHILE Idx IS NOT NULL LOOP

      DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, '999')||LPAD(v(Idx), 7));

      Idx := v.NEXT(Idx);

    END LOOP;

  END;

END;

 

NLS Parameter Values Affect Associative Arrays Indexed by String

National Language Support (NLS) parameters such as NLS_SORT, NLS_COMP, and NLS_DATE_FORMAT affect associative arrays indexed by string.

Changing NLS Parameter Values After Populating Associative Arrays

The initialization parameters NLS_SORT and NLS_COMP determine the storage order of string indexes of an associative array. If you change the value of either parameter after populating an associative array indexed by string, then the collection methods FIRST, LAST, NEXT, and PRIOR (described in "Collection Methods") might return unexpected values or raise exceptions. If you must change these parameter values during your session, restore their original values before operating on associative arrays indexed by string.

Indexes of Data Types Other Than VARCHAR2

In the declaration of an associative array indexed by string, the string type must be VARCHAR2 or one of its subtypes. However, you can populate the associative array with indexes of any data type that theTO_CHAR function can convert to VARCHAR2. (For information about TO_CHAR, see Oracle Database SQL Language Reference.)

If your indexes have data types other than VARCHAR2 and its subtypes, ensure that these indexes remain consistent and unique if the values of initialization parameters change. For example:

            ·         Do not use TO_CHAR(SYSDATE) as an index.

If the value of NLS_DATE_FORMAT changes, then the value of (TO_CHAR(SYSDATE)) might also change.

            ·         Do not use different NVARCHAR2 indexes that might be converted to the same VARCHAR2 value.

            ·         Do not use CHAR or VARCHAR2 indexes that differ only in case, accented characters, or punctuation characters.

If the value of NLS_SORT ends in _CI (case-insensitive comparisons) or _AI (accent- and case-insensitive comparisons), then indexes that differ only in case, accented characters, or punctuation characters might be converted to the same value.

 

Passing Associative Arrays to Remote Databases

If you pass an associative array as a parameter to a remote database, and the local and the remote databases have different NLS_SORT or NLS_COMP values, then:

              ·         The collection method FIRST, LAST, NEXT or PRIOR (described in "Collection Methods") might return unexpected values or raise exceptions.

              ·         Indexes that are unique on the local database might not be unique on the remote database, raising the predefined exception VALUE_ERROR.

Appropriate Uses for Associative Arrays

An associative array is appropriate for:

• A relatively small lookup table, which can be constructed in memory each time you invoke the subprogram or initialize the package that declares it
• Passing collections to and from the database server

Declare formal subprogram parameters of associative array types. With Oracle Call Interface (OCI) or an Oracle precompiler, bind the host arrays to the corresponding actual parameters. PL/SQL automatically converts between host arrays and associative arrays indexed by PLS_INTEGER.

Note:

You cannot declare an associative array type at schema level. Therefore, to pass an associative array variable as a parameter to a standalone subprogram, you must declare the type of that variable in a package specification. Doing so makes the type available to both the invoked subprogram (which declares a formal parameter of that type) and the invoking subprogram or anonymous block (which declares and passes the variable of that type).

Tip:

The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause. For details, see "FORALL Statement" and "BULK COLLECT Clause".

An associative array is intended for temporary data storage. To make an associative array persistent for the life of a database session, declare it in a package specification and populate it in the package body.

I hope you all have enjoyed reading this article. Comments are welcome....