This tip comes from Zlatko Sirotic, Software Developer at Istra Informaticki Inzenjering d.o.o., in
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; /
