oracle 如何解决 dbms_sql.open_cursor 上的 ORA-29471？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20690303/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to solve ORA-29471 on dbms_sql.open_cursor?
提问by Marc Puts
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:
我正在使用 Oracle 11.2.0.1.0 并试图让 dbms_sql 包工作。但是，我不断收到 ORA-29471 错误，如下所示：
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 docssay the following about this:
oracle文档对此有以下说明：
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.
在绑定和执行时进行检查。可选地，可以对每个单个 DBMS_SQL 子程序调用执行检查。支票是：
- current_user 在调用子程序时与调用最新解析时相同。
- 调用子程序时启用的角色必须是调用最新解析时启用的角色的超集。
与定义者权限子程序的使用一致，角色不适用。如果任一检查失败，则会引发 ORA-29470 错误。
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.
Oracle支持（需要登录）网站建议我在 dbms_sql.open_cursor 中明确添加 security_level 参数。添加任何值 (0/1/2) 都不能解决问题。
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.
令我感到困惑的是，我在 . 处收到错误dbms_sql.open_cursor，这是首次定义安全级别的地方。
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.
我还没有尝试过。我更愿意将其视为最后的手段，因为它涉及禁用安全层，并且它是不受支持的 oracle 功能。对于生产使用来说，这几乎不是理想的环境。此外，它根本没有真正解决问题，只是隐藏了它。
How can I solve this error?
我该如何解决这个错误？
回答by Nick Krasnov
The only reason(cannot see another one at this moment) why your code raises the ORA-29471is you already made dbms_sqlinoperable in your session by providing an invalid cursor ID:
您的代码引发的唯一原因（此时无法看到另一个）ORA-29471是您已经dbms_sql通过提供无效的游标 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.
尝试在新建立的会话中执行该代码。
回答by J. Chomel
A solution could be to have a look into the v$Sessionview.
解决方案可能是v$Session查看视图。
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:
如果光标存在于列表中，则表示您仍然可以使用它。然后从它的 中识别它sql_id，你可以检查。在这里生成列表：
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.
更多在这里。
