Populating Associative Arrays

The most efficient way to populate a dense associative array is with a cursor and the FETCH statement with the BULK COLLECT INTO clause, using the following procedure. The procedure uses each necessary statement in its simplest form, but provides references to its complete syntax.

You cannot use the following procedure to populate a sparse associative array. Instead, you must use an assignment statement inside a loop statement. For information about loop statements, see "Controlling Program Flow".

To use a cursor to populate an associative array indexed by integer:

1. If you have not done so, declare an associative array with a cursor, using the procedure in "Declaring Associative Arrays".

2. In the executable part of the PL/SQL unit in which you declared the associative array:

   1. Open the cursor:

      OPEN cursor_name;
      

      For complete OPEN statement syntax, see Oracle Database PL/SQL Language Reference.

   2. Fetch all rows from the cursor into the associative array variable at once, using a FETCH statement with the BULK COLLECT INTO clause:

      FETCH cursor_name BULK COLLECT INTO aa_variable;
      

      For complete FETCH statement syntax, see Oracle Database PL/SQL Language Reference.

   3. Close the cursor:

      CLOSE cursor_name;
      

      For complete CLOSE statement syntax, see Oracle Database PL/SQL Language Reference.

The following example uses the preceding procedure to populate the associative arrays employees_jobs and jobs_, which are indexed by integer. Then it uses an assignment statement inside a statement to populate the associative array job_titles_type, which is indexed by string.

-- Declarative part from Declaring Associative Arrays goes here.
BEGIN
  -- Populate associative arrays indexed by integer:

  OPEN employees_jobs_cursor;
  FETCH employees_jobs_cursor BULK COLLECT INTO employees_jobs;
  CLOSE employees_jobs_cursor;

  OPEN jobs_cursor;
  FETCH jobs_cursor BULK COLLECT INTO jobs_;
  CLOSE jobs_cursor;

  -- Populate associative array indexed by string:

  FOR i IN 1..jobs_.COUNT() LOOP
    job_titles(jobs_(i).job_id) := jobs_(i).job_title;
  END LOOP;
END;
/