Verarbeitung eines dynamischen Selects

06.
September
2006
Veröffentlicht von: Hildegard Asenbauer

Dynamische Selects zu schreiben ist dank Ref Cursoren mittlerweile ziemlich einfach geworden. Was aber tut man, wenn man die Spaltenliste des Select-Befehls nicht kennt?

Dynamische Selects zu schreiben ist dank Ref Cursoren mittlerweile ziemlich einfach geworden. Man muss jedoch wissen, wie viele Spalten man ausliest, und welchen Datentyp sie haben. In der Regel ist das kein Problem, wenn man über die Dynamik nur erreichen will, dass je nach übergebenen Parametern auf unterschiedliche Tabellen zugegriffen wird oder nach unterschiedlichen Spalten sortiert werden soll.

Was aber tut man, wenn man die Spaltenliste des Select-Befehls nicht kennt?

In diesem Fall kann man auf das altbewährte und teilweise bereits als veraltet belächelte DBMS_SQL-Package zurückgreifen. Es ermöglicht, über die Prozedur DESCRIBE_COLUMNS die Spaltenliste zu ermitteln. Diese Prozedur liefert die Anzahl der Spalten zurück und eine Beschreibung jeder Spalte in Form einer INDEX BY-Tabelle, die auf einem Record-Datentyp (DBMS_SQL.DESC_REC) beruht. Ausgelesen werden u. a. Name und Datentyp der Spalten.

Um auch die Anzahl der Spalten komplett dynamisch halten zu können, wurde im unten gezeigten Beispiel mit INDEX BY-Tabellen der wichtigsten Datentypen als Variablen gearbeitet.

Beispiel:

CREATE or replace PROCEDURE GETROWS (p_select IN VARCHAR2) AS
   v_desc     DBMS_SQL.DESC_TAB;
   v_id       INTEGER;
   v_count    INTEGER;
   v_ret      INTEGER;
   Type t_ntype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   v_col_n    t_ntype;
   Type t_vtype IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   v_col_v    t_vtype;
   Type t_dtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;
   v_col_d    t_dtype;
   v_output   VARCHAR2(2000);
BEGIN
   v_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(v_id, p_select, DBMS_SQL.v7);
   DBMS_SQL.DESCRIBE_COLUMNS(v_id, v_count, v_desc);
   
   FOR i in 1..v_count LOOP
      v_col_n(i) := NULL;
      v_col_v(i) := NULL;
      v_col_d(i) := NULL;
   END LOOP;
   
   FOR i in 1..v_count LOOP
      CASE v_desc(i).col_type
         WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
            DBMS_SQL.DEFINE_COLUMN(v_id, i, v_col_v(i), 2000);
         WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
            DBMS_SQL.DEFINE_COLUMN(v_id, i, v_col_n(i));
         WHEN DBMS_TYPES.TYPECODE_DATE THEN
            DBMS_SQL.DEFINE_COLUMN(v_id, i, v_col_d(i));
      END CASE;
   END LOOP;
   
   v_ret := DBMS_SQL.EXECUTE(v_id);
   
   LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(v_id);
      EXIT WHEN v_ret IS NULL OR v_ret <=0;
      FOR i IN 1..v_count LOOP
      v_output := 'Spalteninhalt Spalte '||v_desc(i).col_name ||': ';
         CASE v_desc(i).col_type
            WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
               DBMS_SQL.COLUMN_VALUE(v_id, i, v_col_v(i));
               v_output := v_output||v_col_v(i);
            WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
               DBMS_SQL.COLUMN_VALUE(v_id, i, v_col_n(i));
               v_output := v_output||v_col_n(i);
            WHEN DBMS_TYPES.TYPECODE_DATE THEN
               DBMS_SQL.COLUMN_VALUE(v_id, i, v_col_d(i));
               v_output := v_output||v_col_d(i);
         END CASE;
         DBMS_OUTPUT.PUT_LINE(v_output);
      END LOOP;   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(v_id);
EXCEPTION
   WHEN CASE_NOT_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Unbekannter Datentyp einer Spalte');      
      DBMS_SQL.CLOSE_CURSOR(v_id);
END;
/

SET SERVEROUTPUT ON SIZE 1000000
EXEC GETROWS('SELECT * FROM EMP')

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.