Dynamic cursor

Because I like anything which is "dynamic", I have played with the DBMS_SQL package.
My goal was to send any Select order to a procedure and get a collection of records and a datastructure to manipulate these records.

So, this is the trick:

CREATE OR REPLACE PACKAGE PKG_DYNAMIC IS

TYPE T1 IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER ;
TYPE T2 IS TABLE OF T1 INDEX BY BINARY_INTEGER ;

PROCEDURE dynamic_cursor
(
PC$Query IN VARCHAR2,
PT$Cols OUT T2,
PT$Raws OUT T2
) ;


END PKG_DYNAMIC ;
/


CREATE OR REPLACE PACKAGE BODY PKG_DYNAMIC IS

PROCEDURE dynamic_cursor
(
PC$Query IN VARCHAR2,
PT$Cols OUT T2,
PT$Raws OUT T2
)
IS
c NUMBER;
d NUMBER;
col_cnt PLS_INTEGER;
rec_tab dbms_sql.desc_tab;
col_num NUMBER;

LN$Lig PLS_INTEGER := 0 ;
LN$MaxCol PLS_INTEGER := 0 ;

v VARCHAR2(4000) ;
t T1 ;

source_cursor INTEGER;
result INTEGER;

BEGIN

-- retrieve the columns of the query --
c := dbms_sql.open_cursor;

dbms_sql.parse(c, PC$Query , dbms_sql.NATIVE);

d := dbms_sql.execute(c);

dbms_sql.describe_columns(c, col_cnt, rec_tab);


LN$MaxCol := rec_tab.last ;

For i in rec_tab.first .. rec_tab.last Loop

PT$Cols(i)(1) := rec_tab(i).col_name ; -- name
PT$Cols(i)(2) := rec_tab(i).col_type ; -- type
PT$Cols(i)(3) := rec_tab(i).col_precision ; -- precision
PT$Cols(i)(4) := rec_tab(i).col_scale ; -- scale
PT$Cols(i)(5) := rec_tab(i).col_max_len ; -- length

End loop ;

dbms_sql.close_cursor(c);


-- Prepare a cursor to select from the source table: --
source_cursor := dbms_sql.open_cursor;

dbms_sql.parse(source_cursor, PC$Query, dbms_sql.NATIVE);


-- Define the columns --
For i in 1 .. LN$MaxCol Loop
dbms_sql.DEFINE_COLUMN(source_cursor, i, v,4000);
End loop ;


result := dbms_sql.execute(source_cursor);


-- Fetch the rows from the source query --
LOOP
IF dbms_sql.FETCH_ROWS(source_cursor)>0 THEN
-- get column values of the row --
LN$Lig := LN$Lig + 1 ;
For i in 1.. LN$MaxCol Loop
dbms_sql.COLUMN_VALUE(source_cursor, i, t(i));
PT$Raws(LN$Lig)(i) := t(i) ;
End loop ;

ELSE
-- No more rows --
EXIT;
END IF;
END LOOP;


dbms_sql.close_cursor(source_cursor);


EXCEPTION
WHEN OTHERS THEN

IF dbms_sql.is_open(source_cursor) THEN
dbms_sql.close_cursor(source_cursor);
END IF;

RAISE;

END;

END PKG_DYNAMIC ;
/

I created a table to store the Select orders:

SQL> desc fd.queries
Name Null? Type
----------------------------------------- -------- -----------------
NUM NOT NULL NUMBER(3)
QUERY VARCHAR2(512)


SQL> select * from fd.queries ;

NUM QUERY
---------- ----------------------------------------------------------
1 select empno, ename from emp where rownum <= 1
2 select * from emp where rownum <= 2

Then i used this table to test my package

SQL> set serveroutput on
SQL>
SQL> Declare
2 TabCols PKG_DYNAMIC.T2 ;
3 TabRaws PKG_DYNAMIC.T2 ;
4 cursor C is select query from queries where num = 1;
5 Begin
6 For CC in C Loop
7 PKG_DYNAMIC.dynamic_cursor( CC.query, TabCols, TabRaws ) ;
8 dbms_output.put_line( '*** Columns description ***' ) ;
9 For i in TabCols.first .. TabCols.last Loop
10 dbms_output.put_line
11 (
12 ' Name = ' || TabCols(i)(1) ||
13 ' Type = ' || TabCols(i)(2) ||
14 ' Precision = ' || TabCols(i)(3) ||
15 ' Scale = ' || TabCols(i)(4) ||
16 ' Size = ' || TabCols(i)(5)
17 ) ;
18 End loop ;
19 dbms_output.put_line( '*** Datas ***' ) ;
20 For i in TabRaws.first .. TabRaws.last Loop
21 For j in 1 .. TabRaws(i).Last loop
22 dbms_output.put ( TabRaws(i)(j) || ' - ' ) ;
23 End loop ;
24 dbms_output.put_line('') ;
25 End loop ;
26 End loop ;
27 End ;
28
29 /
*** Columns description ***
Name = EMPNO Type = 2 Precision = 4 Scale = 0 Size = 22
Name = ENAME Type = 1 Precision = 0 Scale = 0 Size = 10
*** Datas ***
7369 - SMITH -

PL/SQL procedure successfully completed.

SQL>