如何在dbms_sql.open_cursor上解决ORA-29471？ [英] How to solve ORA-29471 on dbms_sql.open_cursor?

问题描述

我正在使用Oracle 11.2.0.1.0，并试图使dbms_sql程序包正常工作。
但是，我仍然收到ORA-29471错误，如下所示：

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

oracle 文档对此进行以下说明：

The oracle docs say the following about this:

在绑定和执行时进行检查。 （可选）可以对每个单个DBMS_SQL子程序调用执行
检查。检查是：

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

• current_user在调用子程序时与在调用最新解析时相同。


• 调用子程序时启用的角色必须是调用最新解析时启用的角色的超集。

与定义者权利子程序的使用一致，角色不
适用。如果其中任何一项检查失败，并且引发ORA-29470错误。

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.

Oracle 支持（需要登录）网站建议我将security_level参数明确添加到dbms_sql.open_cursor中。添加任何值（0/1/2）都不能解决问题。

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.

对我来说令人困惑的是，我在<$ c处遇到了错误$ c> dbms_sql.open_cursor ，这是首次定义安全级别的地方。

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;

我还没有尝试过。我宁愿将其视为万不得已的方法，因为它涉及到禁用安全层，并且它是不受支持的oracle功能。几乎不是理想的生产环境。另外，它根本无法解决问题，只是将其隐藏。

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.

我该如何解决此错误？

推荐答案

您的代码引发 ORA-29471 的唯一原因（目前无法看到另一个）通过提供无效的游标ID，使 dbms_sql 在会话中无法运行：

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 

尝试在新建立的会话。

这篇关于如何在dbms_sql.open_cursor上解决ORA-29471？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！