Die Arbeit mit Arrays ist für einen APEX oder PL/SQL Entwickler alltäglich - sie werden immer wieder gebraucht. Und doch erscheint der Umgang mit Arrays in der Oracle-Datenbank oft rätselhaft.

Es gibt drei Array-Typen in der Oracle-Datenbank: VARRAYs, Nested Tables und PL/SQL Assoziative Arrays. Dieser Tipp gibt einen Einblick in die Arbeit mit Arrays in der Oracle Datenbank und stellt vor, was geht und was nicht. In APEX können Arrays überall genutzt werden, wo PL/SQL oder SQL zum Einsatz kommt: Beispiele sind Berichts-SQL oder PL/SQL in einem onSubmit-Prozess. Im ersten Teil des Tipps sind VARRAYs und Nested Tables, die sowohl in SQL als auch in PL/SQL bereitstehen, vorgestellt worden. Die nur in PL/SQL-Programmen verfügbaren assoziativen Arrays werden in diesem Community Tipp behandelt.

Assoziative Arrays (PL/SQL INDEX BY-Tables)

Ein assoziatives Array wird in einem PL/SQL Block zunächst als "type" erzeugt; anschließend kann eine Variable dieses Typs deklariert werden. Das Zuweisen von Werten geschieht danach mit der gleichen Syntax wie bei den im letzten Teil vorgestellten VARRAYs (allerdings ist - im Gegensatz zu diesen - keinerlei Initialisierung nötig). Der folgende Code erzeugt beispielhaft ein assoziatives Array und weist drei Werte zu.

declare
  type t_array is table of varchar2(200) index by binary_integer;
  l_array t_array;
begin
  l_array(0) := 'Oracle';
  l_array(3) := 'Datenbank';
  l_array(33) := 'Application Express';

  dbms_output.put_line('Das Array hat '||l_array.count||' Werte.');
end;
/

Das Array hat 3 Werte.

PL/SQL-Prozedur erfolgreich abgeschlossen.

Weist man einer Indexposition erneut einen Wert zu, so wird der alte überschrieben. Als Datentypen für den Index können Sie BINARY_INTEGER, PLS_INTEGER oder SIMPLE_INTEGER verwenden (NUMBER geht nicht). Neben diesen numerischen Typen sind aber auch VARCHAR2-Werte möglich.

declare
  type t_array is table of varchar2(200) index by varchar2(20);
  l_array t_array;

  l_index varchar(20);
begin
  l_array('A') := 'Oracle';
  l_array('AB') := 'Datenbank';
  l_array('ABAC') := 'Application Express';

  dbms_output.put_line('Das Array hat '||l_array.count||' Werte.');
end;
/

Das Array hat 3 Werte.

PL/SQL-Prozedur erfolgreich abgeschlossen.

Etwas sonderbar verhält sich SQL NULL, verwendet man es als Indexwert. Zuweisungen sind möglich - und mit SQL NULL kann ein Wert auch wieder angerufen werden. Der folgende Code läuft fehlerfrei.

declare
  type t_array is table of varchar2(200) index by binary_integer;
  l_array t_array;
begin
  l_array(0) := 'Oracle';
  l_array(null) := 'Application Express';

  dbms_output.put_line('Wert bei Index = SQL NULL: '||l_array(null));
end;
/

Wert bei Index = SQL NULL: Application Express

PL/SQL-Prozedur erfolgreich abgeschlossen.

Verwendet man jedoch die von PL/SQL bereitgestellten Array-Methoden wie COUNT, NEXT, LAST oder FIRST, so löst die Verwendung von SQL NULL eine Fehlermeldung aus.

declare
  type t_array is table of varchar2(200) index by binary_integer;
  l_array t_array;
begin
  l_array(0) := 'Oracle';
  l_array(1) := 'Datenbank';
  l_array(null) := 'Application Express';

  dbms_output.put_line('Das Array hat '||l_array.count||' Werte.');
end;
/

FEHLER in Zeile 1:
ORA-06502: PL/SQL: numerischer oder Wertefehler: Schlüsselwert NULL auf Indextabelle
ORA-06512: in Zeile 7

Als 'Best Practice' lässt sich daher festhalten, dass man SQL NULL nicht als Indexwert verwenden sollte.

Assoziative Arrays: Zugriff auf nicht vorhandene Indexpositionen

Besonders beachtenswert ist das Verhalten, wenn man eine Arrayposition anspricht, die es nicht gibt.

declare
  type t_array is table of varchar2(200) index by binary_integer;
  l_array t_array;
begin
  l_array(0) := 'Oracle';
  l_array(3) := 'Datenbank';
  l_array(33) := 'Application Express';

  dbms_output.put_line('Das Array hat '||l_array.count||' Werte.');

  dbms_output.put_line('Array mit Index "0" ist: '||l_array(0));
  dbms_output.put_line('Array mit Index "1" ist: '||l_array(1));
end;
/

Das Array hat 3 Werte.
Array mit Index "0" ist: Oracle
declare
*
FEHLER in Zeile 1:
ORA-01403: Keine Daten gefunden
ORA-06512: in Zeile 12

Der ausgelöste ORA-01403: no data found ist für viele verwirrend, denn man kennt den Fehler ansonsten nur von SELECT INTO-Anweisungen. Fehler werden daher oft an der falschen Stelle gesucht. Hier verhält sich ein assoziatives Array anders als VARRAYs oder NESTED TABLEs.

• Wird bei einem VARRAY oder einer NESTED TABLE eine ungültige Position ("über" der Arraygröße) angesprochen, so wird ORA-06533: Subscript beyond count ausgelöst.
• Wird bei einem VARRAY oder einer NESTED TABLE eine gültige Position angesprochen, deren Wert jedoch nicht belegt ist, so wird SQL NULL zurückgegeben.

Beim Zugriff auf eine PL/SQL INDEX BY Table muss man sich also immer vor Augen halten, dass Arraypositionen nicht belegt sein könnten. Daher ist, möchte man das Array komplett auslesen, eine einfache FOR-Schleife nicht der richtige Weg: Man muss daher anders vorgehen und die PL/SQL-Methoden FIRST und NEXT bzw. LAST und PRIOR verwenden.

declare
  type t_array is table of varchar2(200) index by binary_integer;
  l_array t_array;

  l_index binary_integer;
begin
  l_array(0) := 'Oracle';
  l_array(3) := 'Datenbank';
  l_array(33) := 'Application Express';

  dbms_output.put_line('Das Array hat '||l_array.count||' Werte.');
 
  -- Indexvariable auf den ersten Wert initialisieren
  l_index := l_array.first;

  while l_index is not null loop
    -- Arrayinhalt an Position "l_index" auslesen
    dbms_output.put_line('Array Index '||l_index||'='||l_array(l_index));
    -- l_index auf nächste Arrayposition setzen - wird SQL NULL nach der letzten
    l_index := l_array.next(l_index);
  end loop;
end;
/

Das Array hat 3 Werte.
Array Index 0=Oracle
Array Index 3=Datenbank
Array Index 33=Application Express

PL/SQL-Prozedur erfolgreich abgeschlossen.

Assoziative Arrays: Nur in PL/SQL, nicht in SQL

Da assoziative Arrays nur in PL/SQL verwendet werden können, scheidet die Übergabe nach SQL, bspw. durch Aufrufen einer PL/SQL Funktion aus einer SQL-Abfrage, völlig aus.

create or replace package pkg_arraytest is
  type t_plsqlarray is table of varchar2(200) index by binary_integer;
  function get_plsqlarray return t_plsqlarray;
end pkg_arraytest;
/
sho err

create or replace package body pkg_arraytest is
  function get_plsqlarray return t_plsqlarray is
    l_array t_plsqlarray;
  begin
    l_array(1) := 'Oracle';
    l_array(2) := 'Datenbank';
    l_array(3) := 'Application Express';
    return l_array;
  end get_plsqlarray;
end;
/
sho err

SQL> desc pkg_arraytest
FUNCTION GET_PLSQLARRAY RETURNS TABLE OF VARCHAR2(200)

SQL> select pkg_arraytest.get_plsqlarray from dual;
select pkg_arraytest.get_plsqlarray from dual
       *
FEHLER in Zeile 1:
ORA-00902: Ungültiger Datentyp

Möchte man also aus einer PL/SQL-Funktion ein Array zurückgeben und diese Funktion in einer SQL-Anweisung verwenden, kann man nicht mit assoziativen Arrays arbeiten. Dann kommen nur Nested Tables oder VARRAYs in Frage - und der Datentyp muss mit CREATE TYPE direkt auf SQL-Ebene erzeugt werden.

Assoziative Arrays als Implementierungsvariante für Lookup-Tabellen

Assoziative Arrays eignen sich recht gut zur Verwaltung von Lookup-Informationen. Wenn im Rahmen eines PL/SQL-Programms sehr häufig in eine kleine Tabelle hineingesehen werden muss, kann es sinnvoll sein, diese in einem assoziativen Array zwischenzuspeichern. Zum Befüllen aus einer Tabelle eignet sich die BULK COLLECT-Klausel, welche eine ganze Ergebnismengenspalte auf einmal in das assoziative Array übernimmt.

Das folgende Codebeispiel illustriert das: Die Spalten OWNER, OBJECT_NAME und OBJECT_TYPE der Dictionary View ALL_OBJECTS werden in assoziative Arrays geladen. Anschließend wird ein neues assoziatives Array generiert, welches OWNER.OBJECT_NAME als Index und OBJECT_TYPE als Wert enthält. Darin können nun Lookups durchgeführt werden.

declare
  type t_array1 is table of varchar2(200) index by binary_integer;
  type t_array2 is table of varchar2(200) index by varchar2(100);

  l_arrayname  t_array1;
  l_arraytype  t_array1;
  l_arrayowner t_array1;
  l_lookuparray t_array2;
 
  l_idx binary_integer;

  l_time    pls_integer;
begin
  -- Alle Datenbankobjekte in zwei Arrays laden
  select owner, object_name, object_type
  bulk collect into l_arrayowner, l_arrayname, l_arraytype
  from all_objects;

  -- Lookup Array erstellen
  l_idx := l_arrayname.first;
  while l_idx is not null loop
    l_lookuparray(l_arrayowner(l_idx)||'.'||l_arrayname(l_idx)) := l_arraytype(l_idx);
    l_idx := l_arrayname.next(l_idx);
  end loop;

  -- Lookup durchführen
  dbms_output.put_line(l_lookuparray('TESTIT.EMP'));
end;
/

Misst man für die einzelnen Schritte noch die Zeit, so ergibt sich für knapp 67.000 Einträge in ALL_OBJECTS folgendes:

Elapsed for LOAD FROM TABLE: 6,12 sec.
Elapsed for GENERATE LOOKUP: 0,18 sec.
Elapsed for 100.000 LOOKUPS: 0,02 sec.

Die meiste Zeit wird beim initialen Selektieren der Tabelleninhalte in die drei Arrays l_arrayowner, l_arrayname und l_arraytype verbraucht. Das "Umkopieren" in ein neues Array geht dagegen sehr schnell. Und die Lookups selbst sind pfeilschnell: 100.000 Lookup-Operationen verbrauchen gerade einmal 2/100 Sekunden. Führt man diese 100.000 Lookups dagegen "traditionell" per SELECT INTO durch ...

Elapsed for 100.000 SQL LOOKUPS: 7,51 sec.

Man sieht, dass sich der Aufwand in diesem Falle knapp gelohnt hätte - je öfter die Lookup-Operation stattfindet, desto eher lohnt sich das Vorhalten des assoziativen Arrays. Natürlich hat das Erstellen des Arrays auch einen Preis: Im Test hat Oracle für die Arrays insgesamt etwa 16,2MB Hauptspeicher (PGA) allokiert.

Assoziative Arrays in APEX

APEX verwendet assoziative Arrays sehr oft - leider liefert ein reines DESCRIBE der Packages nicht immer genügend Information. Welchen Typ muss eine Variable haben, damit man APEX_UTIL.TABLE_TO_STRING damit aufrufen kann ...?

SQL> desc APEX_UTIL

:
FUNCTION TABLE_TO_STRING RETURNS VARCHAR2
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_TABLE                        TABLE OF VARCHAR2(32767) IN
 P_STRING                       VARCHAR2                IN     DEFAULT
:

Einerseits kann man in die Dokumentation schauen, manchmal fehlt diese jedoch oder man hat gerade keinen Zugriff. In solchen Fällen kann ein Blick in die Dictionary View USER_ARGUMENTS helfen.

select argument_name, data_type, type_name||'.'||type_subname data_type_name 
from all_arguments 
where owner = 'APEX_040200'  
  and object_name   = 'TABLE_TO_STRING' 
  and package_name  = 'HTMLDB_UTIL' 
  and argument_name = 'P_TABLE' 
order by sequence;

ARGUMENT_NAME   DATA_TYPE                 NAME
--------------- ------------------------- -------------------------
P_TABLE         PL/SQL TABLE              WWV_FLOW_GLOBAL.VC_ARR2

1 Zeile wurde ausgewählt.

Die Funktion kann also wie folgt genutzt werden:

declare
  l_array WWV_FLOW_GLOBAL.VC_ARR2;
begin
  l_array(1) := 'Oracle';
  l_array(2) := 'Datenbank';
  l_array(3) := 'Application Express';
  
  dbms_output.put_line(apex_util.table_to_string(l_array));
end;
/

Oracle:Datenbank:Application Express

Analoges gilt für die G_FXX-Arrays, mit deren Hilfe man tabellarische Formulare mit manuellem PL/SQL Code verarbeiten kann.

select argument_name, data_type, type_name||'.'||type_subname data_type_name 
from all_arguments 
where owner = 'APEX_040200'  
  and object_name   = 'ACCEPT' 
  and package_name  = 'WWV_FLOW' 
  and argument_name like 'F__' 
order by sequence;

ARGUMENT_NAME   DATA_TYPE                 NAME
--------------- ------------------------- -------------------------
F01             PL/SQL TABLE              WWV_FLOW_GLOBAL.VC_ARR2
F02             PL/SQL TABLE              WWV_FLOW_GLOBAL.VC_ARR2
F03             PL/SQL TABLE              WWV_FLOW_GLOBAL.VC_ARR2
:
F50             PL/SQL TABLE              WWV_FLOW_GLOBAL.VC_ARR2

Fazit und weitere Informationen

Die Oracle-Datenbank bietet mehrere Möglichkeiten an, mit Arrays zu arbeiten. VARRAYs und Nested Tables erlauben es, Arrays im SQL-Kontext zu nutzen. Es wird möglich, Arrays in einzelnen Tabellenspalten abzulegen und mit SQL auf diese Werte zuzugreifen. Insbesondere, wenn Wertelisten stets als Ganzes gelesen und geschrieben werden sollen, kann das VARRAY eine Alternative zu einer separaten Tabelle sein. VARRAYs und Nested Tables lassen sich auch in PL/SQL nutzen. PL/SQL Entwickler können jedoch auch mit assoziativen Arrays arbeiten: diese stehen nur in PL/SQL zur Verfügung und funktionieren im Detail etwas anders. Welche Variante man wann einsetzt, ist sicherlich von Fall zu Fall unterschiedlich zu entscheiden.

• PL/SQL Language Reference: PL/SQL Collections and Records
  http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CIHIHBIA
• SQL Language Reference: VARRAY column properties of the CREATE TABLE command
  http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#i2143624
• SQL Language Reference: COLLECT Function
  http://docs.oracle.com/cd/E16655_01/server.121/e17209/functions033.htm#i1271564

Zurück zur Community-Seite