Konvertierung von Ref Cursor in dbms_sql Cursor (ab 11g)

02.
Februar
2008
Veröffentlicht von: Marco Patzwahl

Wir hatten vor kurzer Zeit in einem Projekt das Problem, einen Ref Cursor auszuwerten, von dem nicht bekannt war, wie viele Spalten er zurückliefert. Dies ist aber notwendig, um eine entsprechende Anzahl an Parametern (bzw. Records/Arrays) zu definieren.

Wir hatten vor kurzer Zeit in einem Projekt das Problem, einen Ref Cursor auszuwerten, von dem nicht bekannt war, wie viele Spalten er zurückliefert.

Dies ist aber notwendig, um eine entsprechende Anzahl an Parametern (bzw. Records/Arrays) zu definieren.

Ab Version 11g kann man nun einen Ref Cursor in einen dbms_sql Cursor umwandeln. Dann ist es möglich, die Spaltenzahl zu ermitteln und dynamisch Variablen für diese Spalten zu definieren.

Zuerst benötigen wir ein Package, das den Datentyp Ref Cursor als Rückgabewert erlaubt:

CREATE OR REPLACE PACKAGE ref_demo IS
TYPE my_curs_type IS REF CURSOR;
FUNCTION ret_curs( p_order_col IN VARCHAR2 DEFAULT 'ENAME')
RETURN my_curs_type;
END;
/

Dann definieren wir ein Package Body mit einer Funktion, die einen Ref Cursor zurückgibt:

CREATE OR REPLACE PACKAGE BODY ref_demo
is
FUNCTION ret_curs(
p_order_col IN VARCHAR2 DEFAULT 'ENAME') RETURN my_curs_type
IS
curs my_curs_type;
BEGIN
      OPEN curs FOR 'select * from scott.emp order by '||p_order_col||'       desc';
      RETURN curs;
END;
END;
/

Zu guter Letzt schreiben wir einen anonymen Block, der dann den Ref Cursor aus der Funktion zurückbekommt.
Et voila, wir können mit einer beliebigen Spaltenanzahl das Ergebnis weiterverarbeiten:

DECLARE
curs scott.ref_demo.my_curs_type;
cur_id PLS_INTEGER;
p_colcount PLS_INTEGER;
p_cursdesc dbms_sql.desc_tab;
p_colvalue VARCHAR2(4000);
BEGIN
-- Wir rufen unsere Funktion im Package mit dem Parameter auf, nach dem wir sortieren möchten
curs:=scott.ref_demo.ret_curs
('SAL');-- Nun wird der Ref Cursor in einen dbms_sql Cursor konvertiert
cur_id:=dbms_sql.to_cursor_number(curs); --- Neu in 11g
-- Das describe_columns liefert uns Anzahl und Typen der Rückgabemenge
dbms_sql.describe_columns(cur_id,p_colcount,p_cursdesc);
dbms_output.put_line('Anzahl Spalten:'||p_colcount);
-- Nun definieren wir uns für jede Spalte der Rückgabemenge einen Container (hier Varchar2 mit der Länge 4000 Bytes)
FOR i IN 1 .. p_colcount LOOP
            dbms_sql.define_column(cur_id,i,p_colvalue,4000);
END LOOP;
-- Solange Zeilen in der Ergebnismenge gefunden werden gehen wir Spalte für Spalte vor
WHILE (dbms_sql.fetch_rows(cur_id)>0) LOOP
                  FOR i IN 1 .. p_colcount LOOP
                        dbms_sql.column_value(cur_id,i,p_colvalue);
                        -- Im Beispiel geben wir die gefunden Spalten
                        alle in einer Zeile aus
                        dbms_output.put(p_colvalue||';');
                  END LOOP;
                  -- Damit es schöner aussieht am Ende der Zeile einen                   Zeilenumbruch
                  dbms_output.new_line;
      END LOOP;
-- Cursor wird sauber schließen --raus
dbms_sql.close_cursor(cur_id);
-- Hier könnte Ihr Fehlerbehandlungsteil stehen :-). Den haben wir aus Vereinfachungsgründen weggelassen
END;
/

Weitere Informationen zum Thema Neuerungen in 11g erhalten Sie in unserem Kurs Opens internal link in current windowOracle 11g New Features, und weitere Informationen zum Thema Dynamisches SQL erhalten Sie im Opens internal link in current windowPL/SQL II Kurs bei uns im Haus.

DBA PL/SQL

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.