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;
/
The example at right was found in version 23.8 in recover.bsq
TYPE pdbNameList_t IS TABLE OF pdbNameRec_t INDEX BY rc_pdbs.name%TYPE;
rc_pdbs is a view based on the following DDL and "name" is of data type VARCHAR2(128)
CREATE OR REPLACE VIEW rc_pdbs AS
SELECT pdb_key, db_key, name, con_id, dbid, creation_change#, guid
FROM rci_pdbs
WHERE con_id > 1;
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;