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?