昨天我在制作过程中遇到了一个奇怪的错误.
声明执行失败
声明执行失败
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
这似乎是错误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：结果集变量或查询的返回类型不匹配.
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;
