Is there a way to grab a CURSOR type column with DBMS_SQL?

Question

Some context: I was thinking of creating an apex plugin in a certain way, quite specific but reusable in the same project. I've arrived at this question by just researching my options a bit. I don't really need it as a refcursor will cover my needs in that I will always expect a statement which has the identical column names and datatypes. The catch was that the query could be performed on different datasets (think tables with definitions and tables with user data).

So I was thinking: what if trying to deal with this more dynamical?
The cursor statement would allow me to just ask of the user to write his query in the one statement box provided by the interface (if you know apex, the region source sql) and not have split things up or make it complicated. But I'm a bit stumped on how to deal with that. There simply doesn't seem to be any way to deal with this. For example, with the below statement it would be possible to describe a query and determine if a column is a CURSOR type. But it stops there. There is no way to grab this actual cursor and do something with it.

Some test table+data DDL

create table test_a (id number, title varchar2(200))

create table test_b (id number, a_id number, title varchar2(200));

create table test_c (id number, b_id number, title varchar2(200));

insert into test_a (id, title) values (1, 'hoofd 1');
insert into test_a (id, title) values (2, 'hoofd 2');

insert into test_b (id, a_id, title) values (1, 1, 'h1 - child 1');
insert into test_b (id, a_id, title) values (2, 1, 'h1 - child 2');
insert into test_b (id, a_id, title) values (3, 2, 'h2 - child 1');
insert into test_b (id, a_id, title) values (4, 2, 'h2 - child 2');

insert into test_c (id, b_id, title) values (1, 1, 'h1 - c1 - A');
insert into test_c (id, b_id, title) values (2, 2, 'h1 - c2 - B');
insert into test_c (id, b_id, title) values (3, 3, 'h2 - c1 - C');
insert into test_c (id, b_id, title) values (4, 4, 'h2 - c2 - D');

Clean it up:

drop table test_a;
drop table test_b;
drop table test_c;

Like, I understand why the following works, but as you can see, it requires knowledge of the cursor's contents and code should handle it.

declare
  l_stmt  varchar2(32000);
  l_c     sys_refcursor;
  l_vc    varchar2(4000);
  l_sub   sys_refcursor;

  procedure children (p_c in sys_refcursor)
  is
    l_k varchar2(200);
    l_c sys_refcursor;
  begin
    loop
      fetch p_c into l_k, l_c;
      exit when p_c%NOTFOUND;
      dbms_output.put_line('L2 titel: '||l_k);
    end loop;
  end;

begin
  l_stmt := q'[select title
, cursor(
    select title, cursor(
      select title
      from test_c
      where b_id = b.id
    ) 
    from test_b b 
    where a_id = a.id
  )
from test_a a]';

  open l_c for l_stmt;

  loop
    fetch l_c into l_vc, l_sub;
    exit when l_c%NOTFOUND;
    dbms_output.put_line('L1 titel: '||l_vc);
    children(l_sub);
  end loop;

  close l_c;
end;
/

The following code with DBMS_SQL fails. Of course. There is no overload for accepting a CURSOR type as a type in DBMS_SQL.

set serveroutput on
declare
  l_stmt       VARCHAR2(32000);
  l_cur_id     BINARY_INTEGER;
  l_rows       INTEGER;
  l_desctab    DBMS_SQL.DESC_TAB3;
  l_colcnt     NUMBER;
  l_count      BINARY_INTEGER := 0;

  l_varchar    VARCHAR2(4000);
  l_sub        SYS_REFCURSOR;
begin
l_stmt := q'[select title
, cursor(
    select title, cursor(
      select title
      from test_c
      where b_id = b.id
    ) 
    from test_b b 
    where a_id = a.id
  )
from test_a a]';

  l_cur_id := dbms_sql.open_cursor;
  dbms_sql.parse(l_cur_id, l_stmt, dbms_sql.native);
  dbms_sql.describe_columns3(l_cur_id, l_colcnt, l_desctab);

  FOR i IN 1 .. l_colcnt LOOP
  dbms_output.put_line('col '||i||' type: '||l_desctab(i).col_type);
  END LOOP;
  -- describing it works, and would return type id 102, which I suppose is CURSOR.

  dbms_sql.define_column(l_cur_id, 1, l_varchar, 4000);
  dbms_sql.define_column(l_cur_id, 2, l_sub); --> fails, evidently

  --l_rows := dbms_sql.execute(l_cur_id);

  dbms_sql.close_cursor(l_cur_id);
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line('error: '||sqlerrm);
  dbms_sql.close_cursor(l_cur_id);
END;
/

If there is no way, that's fine. Academically, I'd just like to know. Maybe there is some other way to do this. Maybe I'm missing something. I was thinking, maybe assembling plsql code and dynamically executing that, but that sounds far over the top. Just like parsing the statement manually by finding cursor statements, which I won't engage in.
Executing the query in sqldeveloper, by the way, works fine, and it will show the following:

hoofd 1 {<TITLE=h1 - child 1,CURSOR(SELECTTITLE={<TITLE=h1 - c1 - A>,}>,<TITLE=h1 - child 2,CURSOR(SELECTTITLE={<TITLE=h1 - c2 - B>,}>,}
hoofd 2 {<TITLE=h2 - child 1,CURSOR(SELECTTITLE={<TITLE=h2 - c1 - C>,}>,<TITLE=h2 - child 2,CURSOR(SELECTTITLE={<TITLE=h2 - c2 - D>,}>,}

Though that may work for completely other reasons, of course.

Answer 1

SQL Developer is all java which is why we can do this. Basically, in java the result of the cursor expression is presented to us as a ResultSet. That is the same way any sql execution is presented. So from a pure java stance, it's a plain ol' result and all the normal java APIs work just fine.

Now in dbms_sql, there's no equivalent as of right now.

FYI this is what the java looks like

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/XE","klrice","klrice");
PreparedStatement stmt = conn.prepareStatement("select table_name\n" + 
        ", cursor(\n" + 
        "        select column_name\n" + 
        "      from user_tab_columns cols\n" + 
        "      where cols.table_name = tabs.table_name \n" + 
        "  )\n" + 
        "from user_tables tabs");
ResultSet rset = stmt.executeQuery();
while( rset.next() ) {

    // get the first column
    System.out.println(rset.getString(1));


    // get column from cursor expression
    // cast it to a ResultSet
    ResultSet cursor = (ResultSet)  rset.getObject(2);
    while(cursor.next() ) {
        System.out.println("\t"+cursor.getString(1));
    }
}