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>

I do not know, at this moment which application i could find to this, but, maybe later...

Francois