How to solve ORA-29471 on dbms_sql.open_cursor?

Question

I'm using Oracle 11.2.0.1.0 and am trying to get the dbms_sql package to work. However, I keep getting the ORA-29471 error, as shown below:

DECLARE
  c INTEGER;
BEGIN
  c := dbms_sql.open_cursor();
END;

ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1017
ORA-06512: at line 4

The oracle docs say the following about this:

Checks are made when binding and executing. Optionally, checks may be performed for every single DBMS_SQL subprogram call. The check is:

• The current_user is the same on calling the subprogram as it was on calling the most recent parse.
• The enabled roles on calling the subprogram must be a superset of the enabled roles on calling the most recent parse.

Consistent with the use of definer's rights subprograms, roles do not apply. If either check fails, and ORA-29470 error is raised.

As far as I can tell, both conditions don't apply to my code, because the code does not cross schemas.

The Oracle support (requires login) website proposes that I explicitly add the security_level parameter into dbms_sql.open_cursor. Adding any of the values (0/1/2) doesn't solve the issue.

The puzzling thing for me is that I get the error at the dbms_sql.open_cursor, which is where the security level is first defined.

The support website also proposes a workaround that involves setting:

alter system set "_dbms_sql_security_level" = 384 scope=spfile;

I haven't tried that yet. I prefer to think of it as a last resort, because it involves disabling a security layer and it is an unsupported oracle feature. Hardly ideal circumstances for production use. Also, it doesn't really solve the issue at all, just hides it.

How can I solve this error?

Griggs

A solution could be to have a look into the v$Session view. Morer here. – Spermicide

Answer 1

The only reason(cannot see another one at this moment) why your code raises the ORA-29471 is you already made dbms_sql inoperable in your session by providing an invalid cursor ID:

/* dbsm_sql detects invalid cursor ID in this session  */ 
SQL> declare
  2    c_1 number := 5;  -- invalid cursor ID. There is no cursor 
  3    l_res boolean;    -- opened with ID = 5     
  4  begin
  5    l_res := dbms_sql.is_open(c_1);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied 
ORA-06512: at "SYS.DBMS_SQL", line 1104 
ORA-06512: at line 5 


/* An attempt to execute this simple anonymous PL/SQL block after 
   an invalid cursor ID has already been detected by the dbms_sql 
   in the current session will lead to ORA-29471 error  
*/

SQL> declare
  2    c_2 number;
  3  begin
  4    c_2 := dbms_sql.open_cursor();
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied 
ORA-06512: at "SYS.DBMS_SQL", line 1084 
ORA-06512: at line 4 

Try to execute that code in a newly established session.

Barina

Dammit. I was about to say exactly the same thing, but you beat me to it. +1 – Cancellate

I had just repro'd the error in the same way as you and was about to paste the SQL*Plus output into my answer when I saw your answer. If I was that upset about it I wouldn't have upvoted your post. :) – Cancellate

You are correct. Establishing a new session fixed the issue. I can't believe I spent over an hour without finding that out. Thanks! – Griggs

@Nicholas Krasnov How can we identify a particular session is invalidated for DBMS_SQL? – Hidden

I'm in Oracle SQL Developer. New session worked. But it actually does not mean opening a new sheet but closing the Developer and Start new. – Regelate

@NickKrasnov wow I can't believe that reopening a new session fixes the issue. I was able to save hours thanks to your answer. Can I ask you to maybe emphasize the establish new connection part? Some non-eagle-eyed users may not realize this. Thanks :) – Garmon

Answer 2

A solution could be to have a look into the v$Session view.

If the cursor exists in the list, then it means you can still us it. Then identify it from its sql_id, and you can check. Here you generate the list:

  select  sql_id, sql_text, count(*) as "OPEN CURSORS", user_name 
   from v$open_cursor
  where user_name <>'SYS' 
group by sql_text, user_name 
order by count(*) desc;

More here.

Spermicide