Dynamische Selects zu schreiben ist dank des 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.
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')