Oracle | Associative | Arrays and Index by Binary Integer Arrays

Oracle Arrays Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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; /