Oracle Arrays
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
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
Bulk Collection and FORALL
Procedures
What's New In 12cR1
What's New In 12cR2

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