Skip to Main Content
 
Titelbild Muniqsoft Training

Search Results

Oracle Tipp Text

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

Bereich:DBA, Version: ab RDBMS 11.x, Letzte Überarbeitung: 25.05.2020

Keywords:Oracle REF_Cursor in SQL Kovertieren

Wir hatten 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.

Seit 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); --- Ab 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 Interessente Artikel zum Thema:


Empfohlene Schulungen zum Thema:
2026.02.18