Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Tomasz.

Asked: September 01, 2009 - 10:17 am UTC

Last updated: September 05, 2019 - 8:23 am UTC

Version: 11.1.0

Viewed 50K+ times! This question is

You Asked

I have a problem in procedure:
procedure ..
begin
v_c := dbms_sql.open_cursor;
v_c2 := dbms_sql.open_cursor;

dbms_sql.parse(v_c, v_sql, dbms_sql.native);
dbms_sql.parse(v_c2, v_query, dbms_sql.native);

v_stmt := dbms_sql.execute(v_c2);
v_cursor := dbms_sql.to_refcursor(v_c2);
dbms_sql.close_cursor(v_c2);

v_stmt := dbms_sql.execute(v_c);
dbms_sql.close_cursor(v_c);
end;

when I execute this procedure, result is
Error report:
ORA-29471: DBMS_SQL access denied

and Tom said...

you are attempting to use dbms_sql to close a cursor that is not a dbms_sql cursor anymore - it looks to us like you are trying to be a "trojan horse", where you close someone elses stuff not opened with dbms_sql and reopen your own sql.

so, we prevent it.

just close the refcursor.

ops$tkyte%ORA11GR1> create or replace procedure p( p_sql in varchar2, p_query in varchar2 )
  2  as
  3  v_c  number := dbms_sql.open_cursor;
  4  v_c2 number := dbms_sql.open_cursor;
  5  v_stmt number;
  6  v_cursor sys_refcursor;
  7  begin
  8
  9  dbms_sql.parse(v_c, p_sql, dbms_sql.native);
 10  dbms_sql.parse(v_c2, p_query, dbms_sql.native);
 11
 12  v_stmt := dbms_sql.execute(v_c2);
 13  v_cursor := dbms_sql.to_refcursor(v_c2);
 14  dbms_sql.close_cursor(v_c2);
 15
 16  v_stmt := dbms_sql.execute(v_c);
 17  dbms_sql.close_cursor(v_c);
 18  end;
 19  /

Procedure created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec p( 'begin null; end;', 'select * from dual' );
BEGIN p( 'begin null; end;', 'select * from dual' ); END;

*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1046
ORA-06512: at "OPS$TKYTE.P", line 14
ORA-06512: at line 1


ops$tkyte%ORA11GR1> connect /
Connected.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace procedure p( p_sql in varchar2, p_query in varchar2 )
  2  as
  3  v_c  number := dbms_sql.open_cursor;
  4  v_c2 number := dbms_sql.open_cursor;
  5  v_stmt number;
  6  v_cursor sys_refcursor;
  7  begin
  8
  9  dbms_sql.parse(v_c, p_sql, dbms_sql.native);
 10  dbms_sql.parse(v_c2, p_query, dbms_sql.native);
 11
 12  v_stmt := dbms_sql.execute(v_c2);
 13  v_cursor := dbms_sql.to_refcursor(v_c2);
 14<b>
 15  CLOSE V_CURSOR;
 16</b>
 17  v_stmt := dbms_sql.execute(v_c);
 18  dbms_sql.close_cursor(v_c);
 19  end;
 20  /

Procedure created.

ops$tkyte%ORA11GR1> exec p( 'begin null; end;', 'select * from dual' );

PL/SQL procedure successfully completed.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

same error message

Boris, March 28, 2013 - 2:13 pm UTC

I had spent more than 3 hours and I could not find much information on the search engine to resolve this issue. Please help me. Thanks in advance.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>


SQL> declare
  2  lcursor number;                       -- for DBMS_SQL Cursor ID
  3  lreturn number;                       -- for DBMS_SQL Cursor ID
  4
  5  lref_cursor sys_refcursor;            -- for REF CURSOR
  6  type        t_emptab is table of ctx_context%rowtype;
  7  lemp_tab    t_emptab;
  8  begin
  9  lcursor := dbms_sql.open_cursor;
 10  dbms_sql.parse(lcursor,
 11     'Select * from emp where rownum=1',DBMS_SQL.NATIVE);
 12  lreturn := dbms_sql.execute(lcursor);
 13                     -- convert from dbms_sql Cursor ID to a REF CURSOR
 14  lref_cursor := dbms_sql.to_refcursor(lcursor);
 15  fetch lref_cursor bulk collect into lemp_tab;
 16  dbms_output.put_line('Employee count: '||lemp_tab.count);
 17  close lref_cursor;
 18  end;
 19
 20  /
declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1163
ORA-06512: at line 9

Tom Kyte
March 29, 2013 - 4:04 pm UTC

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?

Boris, April 04, 2013 - 10:35 am UTC

It's working as you described. I made a silly mistake. I am extremely sorry for the delayed response. Thanks a lot for your time.

what if Incase of colums of different tables are joined

Kartik Kolachina, December 14, 2017 - 5:17 am UTC

I have a similar issue, but in my case the query will fetch records from different tables.
Error:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1084
ORA-06512: at "STCFD001PROC.PA_REPORTS", line 191
ORA-06512: at line 17
</>
BEGIN
c := DBMS_SQL.open_cursor;
DBMS_OUTPUT.PUT_LINE(composedquery);
DBMS_SQL.parse (c, composedquery, DBMS_SQL.native);

l_good_records := DBMS_SQL.EXECUTE(c);

DBMS_OUTPUT.PUT_LINE(l_good_records);
curResult := DBMS_SQL.to_refcursor(c);
acurOutputResult := curResult;

LOOP
FETCH acurOutputResult INTO l_name, l_id, l_value,l_a_name;
DBMS_OUTPUT.PUT_LINE('Name => ' || l_name || '| ID=> ' || l_id || '| value=> ' || l_value || '| Att Name=> ' || l_a_name);
EXIT WHEN acurOutputResult%NOTFOUND;
END LOOP;
--acurOutputResult := curResult;
close acurOutputResult;
--DBMS_SQL.close_cursor (c);
END;
</>
Connor McDonald
December 14, 2017 - 7:54 am UTC

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.

Very useful post

Alexander, July 22, 2019 - 12:26 pm UTC

The ORA-29470 will be resolved after executing the following grant -
GRANT EXECUTE ON DBMS_SQL TO USERNAME;

Session breaking

Eric Larson, September 04, 2019 - 4:20 pm UTC

I haven't seen this mentioned.

In 12.2 this behavior holds true. If, during your coding and testing of dbms_sql, you encounter the Access Denied error, your session will be corrupted.

Even if you fix your code, you will continue to get this error within the same session. In my testing, the only recourse is to close your session and establish a new one.

It's an easily replicated error in my environment. I'm not sure if it holds true in all versions.

-- This will cause an error for the session
DECLARE
v_cursor int := dbms_sql.open_cursor;
BEGIN
dbms_sql.close_cursor( v_cursor );
dbms_sql.close_cursor( v_cursor );
END;
/

-- Running this code subsequently, in the same session, even though it is correct, will still cause the Access Denied error
DECLARE
v_cursor int := dbms_sql.open_cursor;
BEGIN
dbms_sql.close_cursor( v_cursor );
END;
/

Chris Saxon
September 05, 2019 - 8:23 am UTC

Yep, this is a change in 11g.

As MOS note 760560.1 states:

Trying to close a cursor that is already closed using dbms_sql.close_cursor will raise this exception because of the new security enhancements in dbms_sql.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library