过程中出现奇怪的错误"Ora-01001 Invalid cursor"

Question

昨天，我处理了我们生产过程中的一个奇怪的错误。语句执行失败

if v_cursor%isopen then
  close v_cursor; -- here was an error 
end if;

经过一些深入研究，我发现问题出在打开此光标的子程序中。我通过在子程序中添加输出参数sys_refcursor来修复错误。要澄清情况，请考虑以下测试代码：

procedure nested_test(test  number,
                        p_cur out sys_refcursor)
  is  
    procedure nested_procedure_fail is
    begin      
      open p_cur for
        select 1, 2, 3, 4
          from dual
         where 1 = 0;
    end;
    procedure nested_procedure_success(p_cur out sys_refcursor) is
    begin
      open p_cur for
        select 1, 2, 3, 4
          from dual
         where 1 = 0;
    end;
  begin
    if test = 1 then
      nested_procedure_fail;
    else
      if test = 2 then
        nested_procedure_success(p_cur => p_cur);
      else
        open p_cur for
          select 6, 7, 8, 9
            from dual
           where 1 = 1;
      end if;
    end if;
  end;
  procedure test_fail is
    v_cur sys_refcursor;
  begin
    nested_test(test => 1, p_cur => v_cur);
    if v_cur%isopen then
      close v_cur;
    end if;
  end;
  procedure test_success is
    v_cur sys_refcursor;
  begin
    nested_test(test => 2, p_cur => v_cur);
    if v_cur%isopen then
      close v_cur;
    end if;
  end;

如果我尝试运行test_success一切正常，但在test_fail我收到一条消息

ORA-01001：无效游标

我找不到有关此的任何信息。谁能解释为什么这段代码失败？

甲骨文版本：

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Answer 1

这

似乎是错误7174888，或者至少与它密切相关。对此的描述是"sys_refcursor传递给另一个过程时引发的 ORA-6504"，但如果我更改test_fail以进行获取，我也可以做到这一点：

  procedure test_fail is
    v_cur sys_refcursor;
    a number;
    b number;
    c number;
    d number;
  begin
    nested_test(test => 1, p_cur => v_cur);
    if v_cur%isopen then
      fetch v_cur into a,b,c,d;
      close v_cur;
    end if;
  end;

我得到ORA-06504: PL/SQL: Return types of Result Set variables or query do not match.

错误报告中的解决方法可以解决获取和关闭问题。

将 ref 游标初始化为最高级别的非 NULL 值 它将被访问

  begin
    /* Dummy open to avoid bug 7174888 */
    open v_cur for 'select 1 from dual';
    nested_test(test => 1, p_cur => v_cur);
    if v_cur%isopen then
      fetch v_cur into a,b,c,d;
      close v_cur;
    end if;
  end;

Answer 2

一个有趣的问题！ 只是想补充一些东西。

对我来说，真正的问题在于取决于IS_OPEN来确定游标是否有效。 Oracle 可能出于多种原因抛出INVALID_CURSOR，并且可能存在无效的"打开"游标。 假设打开的游标一定有效似乎是合理的（因此我们可以从中获取或执行其他操作，例如简单的关闭），但事实并非如此。

例如，不能在远程过程调用中使用游标变量（通过数据库链接）。 即使使用 Alex 的解决方法，如果在 1 个数据库实例上调用 open，而在另一个数据库实例上调用获取（如果nested_test，任何版本，在 db_A 上定义，然后从 db_B 调用），则相同的示例也会失败。 但是，ISOPEN 的测试仍将返回 TRUE，但随后尝试使用游标（获取）将失败。

可以出于其他原因引发INVALID_CURSOR（例如超出最大打开游标数，或者有时打开游标并等待一段时间后再尝试使用它）。

综上所述，据我所知，没有"ISVALID"测试。 imo 的最佳方法是在同一程序或子程序中打开、获取和关闭游标。 创建一个负责打开游标的过程对我来说有点奇怪（但我确信有一些原因），并且可能导致难以解释的问题（例如这个）。 如果必须让另一个程序为您打开游标，则可能需要将获取并最终关闭游标的代码括在匿名块中，并捕获INVALID_CURSOR异常。

只是我的漫谈;-）