Ref Cursor Problems

[tomate4711]

Suppose you've got a function that is based on a dynamically generated query that returns a ref cursor variable. Now suppose you want to use this ref cursor variable in your procedure, but you don't know the record structure. So how do you make a "FETCH l_ref_cur INTO record_variable" when you don't know the record variable structure.

[tamilselvan]

Hi,
You did not frame the question. You took the question from another web site. I took the answer from the same web site.
Here it is:

Tip of the Week
Tip for Week of April 20, 2003

Dynamic Ref Cursor with Dynamic Fetch

This tip comes from Zlatko Sirotic, Software Developer at Istra Informaticki Inzenjering d.o.o., in Pula, Croatia.

Because ref cursors do not (directly) support description, the solution is quite complicated and requires that the function (or package) return not only a ref cursor variable but a (dynamically) generated query, too.

I am going to use "a good old" DBMS_SQL package and its PARSE and DESCRIBE_COLUMNS procedures in order to make an unknown record variable.

1. Make the "generic" package.

First I am going to make a "dyn_fetch" package in which the "describe_columns" procedure (using query recorded in a global "g_query" variable) creates a "g_desc_tab" PL/SQL table used by the "record_def" function for making a record structure:

CREATE OR REPLACE PACKAGE dyn_fetch IS
   TYPE ref_cur_t IS REF CURSOR;


   g_query    VARCHAR2 (32000);
   g_count    NUMBER;
   g_desc_tab DBMS_SQL.DESC_TAB;

   varchar2_type CONSTANT PLS_INTEGER := 1;
   number_type   CONSTANT PLS_INTEGER := 2;
   date_type     CONSTANT PLS_INTEGER := 12;
   rowid_type    CONSTANT PLS_INTEGER := 11;
   char_type     CONSTANT PLS_INTEGER := 96;

   long_type     CONSTANT PLS_INTEGER := 8;
   raw_type      CONSTANT PLS_INTEGER := 23;
   mlslabel_type CONSTANT PLS_INTEGER := 106;
   clob_type     CONSTANT PLS_INTEGER := 112;
   blob_type     CONSTANT PLS_INTEGER := 113;
   bfile_type    CONSTANT PLS_INTEGER := 114;

   PROCEDURE describe_columns;
   FUNCTION record_def RETURN VARCHAR2;
END;

/

CREATE OR REPLACE PACKAGE BODY dyn_fetch IS
   PROCEDURE describe_columns IS
      l_cur INTEGER;
   BEGIN
      l_cur := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE            (l_cur, g_query, DBMS_SQL.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS (l_cur, g_count, g_desc_tab);
      DBMS_SQL.CLOSE_CURSOR     (l_cur);
   EXCEPTION

      WHEN OTHERS THEN
         IF DBMS_SQL.IS_OPEN (l_cur) THEN
            DBMS_SQL.CLOSE_CURSOR (l_cur);
         END IF;
         RAISE;
   END;

   FUNCTION record_def RETURN VARCHAR2 IS
      l_record_def    VARCHAR2 (32000);
      l_type          VARCHAR2 (100);
      l_col_type      PLS_INTEGER;
      l_col_max_len   PLS_INTEGER;

      l_col_precision PLS_INTEGER;
      l_col_scale     PLS_INTEGER;
   BEGIN
      FOR i IN 1..g_count LOOP
         l_col_type      := g_desc_tab(i).col_type;
         l_col_max_len   := g_desc_tab(i).col_max_len;
         l_col_precision := g_desc_tab(i).col_precision;
         l_col_scale     := g_desc_tab(i).col_scale;

         IF    l_col_type = varchar2_type THEN
            l_type := 'VARCHAR2(' || l_col_max_len || ')';
         ELSIF l_col_type = number_type THEN
            l_type := 'NUMBER(' || l_col_precision || ',' || l_col_scale

|| ')';
         ELSIF l_col_type = date_type THEN
            l_type := 'DATE';
         ELSIF l_col_type = rowid_type THEN
            l_type := 'ROWID';
         ELSIF l_col_type = char_type THEN
            l_type := 'CHAR(' || l_col_max_len || ')';
      -- ELSIF  l_col_type = ...
            -- long_type, raw_type ...
         END IF;

         l_record_def 
         := l_record_def || ' col_' || i || ' ' || l_type|| ',';
      END LOOP;


      l_record_def := RTRIM (l_record_def, ',');
      RETURN l_record_def;
   END;
END;
/

Note that the RECORD_DEF procedure creates column names as col_1 (col_2, ...) because the SELECT clause in the query can be without aliases, for example, "SELECT deptno || dname FROM dept".

2. Create the package that returns the query and ref cursor.

The function that returns the ref cursor variable should return the query, too. So it's better to make two separate functions and put them into the package.

The "set_query" procedure saves the query into the global package variable and the ref cursor is returned by the "ref_cur" function:

CREATE OR REPLACE PACKAGE test IS

   PROCEDURE set_query (p_query VARCHAR2 := NULL);
   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t;
END;
/

CREATE OR REPLACE PACKAGE BODY test IS
   PROCEDURE set_query (p_query VARCHAR2 := NULL) IS
      l_query VARCHAR2 (32000) :=
      '  SELECT e.empno, e.ename,'   ||
      '         e.deptno, d.dname'   ||
      '    FROM emp  e,'             ||
      '         dept d'              ||
      '   WHERE e.deptno = d.deptno';
   BEGIN
      IF p_query IS NULL THEN
         dyn_fetch.g_query := l_query;
      ELSE

         dyn_fetch.g_query := p_query;
      END IF;
   END;

   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t IS
      l_ref_cur dyn_fetch.ref_cur_t;
   BEGIN
      OPEN l_ref_cur FOR dyn_fetch.g_query;
      RETURN l_ref_cur;
   END;
END;
/

So why do I need two separate procedures (functions) in the package?

a) The receiving program must use dynamic SQL, but in the dynamic block I can access only PL/SQL code elements that have a global scope (standalone functions and procedures, and elements defined in the specification of a package). Unfortunately, cursor variables cannot be defined in the specification of a package (so they cannot be global variables).

b) The receiving program must get the column list before ref cursor.

So, there are two options:

a.) Call (in the receiving program) the same function two times (once to get the column list and once to return a ref cursor), or

b.) Use one procedure (or function) for returning query (to get the column list) and a second function for returning a ref cursor.

3. Create the receiving program.

Finally I create a procedure that reads the ref cursor. First, the procedure calls the "test.set_query" and "dyn_fetch.describe_columns" in order to get dynamically generated record structure through the "dyn_fetch.record_def" function and to get process definition through (internal) "process_def" function (in this case, to show rows with DBMS_SQL.PUT_LINE):

CREATE OR REPLACE PROCEDURE test_fetch_ref_cur (p_query VARCHAR2 :=
NULL) IS
   l_statement VARCHAR2 (32000);

   FUNCTION process_def RETURN VARCHAR2 IS

      l_process_def VARCHAR2 (32000);
   BEGIN
      l_process_def := 'DBMS_OUTPUT.PUT_LINE (';

      FOR i IN 1 .. dyn_fetch.g_count LOOP
         l_process_def := l_process_def || ' l_record.col_' || i || ' ||
''>>'' || ';
      END LOOP;

      l_process_def := RTRIM (l_process_def, ' || ''>>'' || ') || ');';
      RETURN l_process_def;
   END;
BEGIN
   test.set_query (p_query);
   dyn_fetch.describe_columns;

   l_statement :=
   '  DECLARE'                              ||
   '     TYPE record_t IS RECORD ('         ||
            dyn_fetch.record_def || ');'    ||
   '     l_record  record_t;'               ||

   '     l_ref_cur dyn_fetch.ref_cur_t;'    ||
   '  BEGIN'                                ||
   '     l_ref_cur := test.ref_cur;'        ||
   '     LOOP'                              ||
   '        FETCH l_ref_cur INTO l_record;' ||
   '        EXIT WHEN l_ref_cur%NOTFOUND;'  ||
            process_def                     ||
   '     END LOOP;'                         ||
   '     CLOSE l_ref_cur;'                  ||
   '  END;';

   EXECUTE IMMEDIATE l_statement;
END;
/

I can test this with:


SET SERVEROUTPUT ON;

EXECUTE test_fetch_ref_cur;

Note that I can try to use a more generic solution. If I take a look at the "test_fetch_ref_cur" procedure, I can see that the part I use for loop can be used in more cases. So I move this part into the "dyn_fetch" package, into the "fetch_ref_cur" procedure to which two parameters has to be sent: the process description and the function name that returns ref cursor.

Here's the changed package:

CREATE OR REPLACE PACKAGE dyn_fetch IS

   -- SAME AS BEFORE, PLUS:

   PROCEDURE fetch_ref_cur (
      p_function_ref_cur VARCHAR2,
      p_process_def      VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY dyn_fetch IS


   -- SAME AS BEFORE, PLUS:

   PROCEDURE fetch_ref_cur (
      p_function_ref_cur VARCHAR2,
      p_process_def      VARCHAR2)
   IS
      l_statement VARCHAR2 (32000);
   BEGIN
      l_statement :=
      '  DECLARE'                              ||
      '     TYPE record_t IS RECORD ('         ||
                record_def || ');'             ||
      '     l_record  record_t;'               ||
      '     l_ref_cur dyn_fetch.ref_cur_t;'    ||
      '  BEGIN'                                ||
      '     l_ref_cur := '                     ||
               p_function_ref_cur || ';'       ||
      '     LOOP'                              ||
      '        FETCH l_ref_cur INTO l_record;' ||
      '        EXIT WHEN l_ref_cur%NOTFOUND;'  ||
               p_process_def                   ||
      '     END LOOP;'                         ||
      '     CLOSE l_ref_cur;'                  ||

      '  END;';

      EXECUTE IMMEDIATE l_statement;
   END;
END;
/

And here's the changed procedure "test_fetch_ref_cur":

CREATE OR REPLACE PROCEDURE test_fetch_ref_cur (p_query VARCHAR2 :=
NULL) IS
   FUNCTION process_def RETURN VARCHAR2 IS
     -- SAME AS BEFORE
   END;
BEGIN
   test.set_query (p_query);
   dyn_fetch.describe_columns;
   dyn_fetch.fetch_ref_cur (
      p_function_ref_cur => 'test.ref_cur',
      p_process_def      => process_def);
END;
/

Tamil

[tomate4711]

Hello Tamil,

yes you have right, that i took this question from an other forum, but my english is so bad. I search for this thema in the web and Í ask the same question in germany in a forum.

So if i found, that what i will ask in good words, so i will take it.

Thanks for your help.