Oracle Arrays
Version 12.1.0.1

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

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;
/
 
Binary Integer Array
Create, load and accessing an array indexed by binary integer conn uwclass/uwclass

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

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-2014 Daniel A. Morgan All Rights Reserved