Oracle Arrays
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Array Syntax TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
 
Associative Array
Note: An associative array in PL/SQL is similar to its counterpart in Perl: An array indexed by a string rather than by an integer.
Create, load and accessing an associative array conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
 TYPE assoc_array IS TABLE OF VARCHAR2(30)
 INDEX BY VARCHAR2(30);


 state_array assoc_array;
BEGIN
  state_array('Alaska') := 'Juneau';
  state_array('California') := 'Sacramento';
  state_array('Oregon') := 'Salem';
  state_array('Washington') := 'Olympia';

  dbms_output.put_line(state_array('Alaska'));
  dbms_output.put_line(state_array('California'));
  dbms_output.put_line(state_array('Oregon'));
  dbms_output.put_line(state_array('Alaska'));
END;
/
Working with associative arrays

The demo at right is a modification of a technique published in Steven Feuerstein's blog
conn scott/tiger@pdbdev

CREATE OR REPLACE PACKAGE scott_array AUTHID DEFINER IS
 TYPE emp_t IS TABLE OF emp%ROWTYPE
 INDEX BY PLS_INTEGER;

 TYPE emp_by_name_t IS TABLE OF emp%ROWTYPE
 INDEX BY emp.ename%TYPE;


 g_emp emp_t;
END scott_array;
/

CREATE OR REPLACE PACKAGE BODY scott_array IS
BEGIN
  SELECT * BULK COLLECT INTO g_emp
  FROM emp;
END scott_array;
/

DECLARE
 l_emp_by_name scott_array.emp_by_name_t;
 l_index VARCHAR2(10);
BEGIN
  FOR indx IN 1 .. scott_array.g_emp.COUNT LOOP
    l_emp_by_name(scott_array.g_emp(indx).ename) := scott_array.g_emp(indx);
  END LOOP;

  l_index := l_emp_by_name.FIRST;
  WHILE (l_index IS NOT NULL) LOOP
    dbms_output.put_line (l_emp_by_name(l_index).ename);
    l_index := l_emp_by_name.NEXT (l_index);
  END LOOP;
END;
/

BEGIN
  FOR rec IN (SELECT * FROM TABLE (scott_array.g_emp) ORDER BY ename) LOOP
    dbms_output.put_line (rec.ename);
  END LOOP;
END;
/
 
Binary Integer Array
Create, load and accessing an array indexed by binary integer conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
 TYPE bin_array IS TABLE OF VARCHAR2(30)
 INDEX BY BINARY_INTEGER;


 state_array bin_array;
BEGIN
  state_array(1) := 'Alaska';
  state_array(2) := 'California';
  state_array(3) := 'Oregon';
  state_array(4) := 'Washington';

  FOR i IN 1 .. state_array.COUNT LOOP
    dbms_output.put_line(state_array(i));
  END LOOP;
END;
/

CREATE TABLE t (
resultcol VARCHAR2(20));

DECLARE
 TYPE bin_array IS TABLE OF VARCHAR2(30)
 INDEX BY BINARY_INTEGER;


 state_array bin_array;
BEGIN
  state_array(1) := 'Alaska';
  state_array(2) := 'California';
  state_array(3) := 'Oregon';
  state_array(4) := 'Washington';

  FORALL i IN 1 .. state_array.COUNT
  INSERT INTO t VALUES (state_array(i));
  COMMIT;
END;
/

SELECT * FROM t;
 
Binary Integer Array
Comparison of associative arrays and arrays indexed by binary integer conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
 TYPE ntab IS TABLE OF NUMBER
 INDEX BY BINARY_INTEGER;

 p ntab;

 TYPE vtab IS TABLE OF NUMBER
 INDEX BY VARCHAR2(100);

 p1 vtab;
 q NUMBER;
BEGIN
  q := dbms_utility.get_time;

  -- begin standard array load
  FOR i IN 1 .. 100000
  LOOP
    p(i) := i;
  END LOOP;

  dbms_output.put_line(dbms_utility.get_time-q);

  q := dbms_utility.get_time;
  -- begin associative array load
  FOR i IN 1 .. 100000
  LOOP
    p1('STUFF'|| TO_CHAR(i)) := i;
  END LOOP;

  dbms_output.put_line(dbms_utility.get_time-q);
END;
/

Related Topics
Bulk Collection and FORALL
Procedures

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2016 Daniel A. Morgan All Rights Reserved