seems to work if I create the necessary tables.
ops$tkyte%ORA11GR2> create table ctx_context ( x int, y int );
Table created.
ops$tkyte%ORA11GR2> create table emp as select * from ctx_context;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 lcursor number; -- for DBMS_SQL Cursor ID
3 lreturn number; -- for DBMS_SQL Cursor ID
4 lref_cursor sys_refcursor; -- for REF CURSOR
5 type t_emptab is table of ctx_context%rowtype;
6 lemp_tab t_emptab;
7 begin
8 lcursor := dbms_sql.open_cursor;
9 dbms_sql.parse(lcursor,
10 'Select * from emp where rownum=1',DBMS_SQL.NATIVE);
11 lreturn := dbms_sql.execute(lcursor);
12 -- convert from dbms_sql Cursor ID to a REF CURSOR
13 lref_cursor := dbms_sql.to_refcursor(lcursor);
14 fetch lref_cursor bulk collect into lemp_tab;
15 dbms_output.put_line('Employee count: '||lemp_tab.count);
16 close lref_cursor;
17 end;
18 /
Employee count: 0
PL/SQL procedure successfully completed.
you'll need to post an entire, complete, 100% there test case to demonstrate the issue with - showing all of the steps from start to finish.
However, that said:
ops$tkyte%ORA11GR2> !oerr ora 29471
29471, 00000, "DBMS_SQL access denied"
// *Cause: DBMS_SQL access was denied due to security concerns.
// *Action: Check the alert log and trace file for more information.
have you done that yet?