oracle – 措施中呈现稀疏的错误“Ora-01001无效游标”

昨天我在建造进程中碰着了一个稀疏的错误.
声明执行失败

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

颠末一番发掘,我发明题目呈此刻打开这个光标的子措施中.我通过在子措施中添加输出参数sys_refcursor来修复bug.为了澄清环境,请思量以下测试代码：

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,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: Invalid cursor

我找不到任何干于此的信息.谁能表明为什么这段代码失败了？

Oracle版本：

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

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：功效集变量或查询的返回范例不匹配.

错误陈诉中的办理要领可以办理获取和封锁题目.

Initialize the ref cursor to a non-NULL value at the highest level at
which it will be accessed

begin
    /* Dummy open to avoid bug 7174888 */
    open v_cur for 'select 1 from dual';
    nested_test(test => 1,d;
      close v_cur;
    end if;
  end;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!