Oracle Arrays
Version 18.2.0.0

General Information
Library Note Morgan's Library Page Header
Are you prepared for the release of Oracle Database 18c ... the first autonomous database? We are here at the Library. It is time for DBAs to stop fighting robots with their fingers and losing ... time to start using our intelligence and winning.
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('Washington'));
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
Built-in Functions
Built-in Packages
Bulk Collection and FORALL
What's New In 12cR2
What's New In 18cR1

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