Dynamic Ref Cursor with Dynamic Fetch

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

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.

Because ref cursors do not (directly) support description, the solution is quite complicated and requires that the function (or package) returns 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;

/