oracle 程序中出现奇怪的错误“Ora-01001 无效光标”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11341166/
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
Strange error "Ora-01001 Invalid cursor" in procedure
提问by Axm
Yesterday I worked on a strange bug in our production procedure. Execution failed on statement
昨天我研究了我们生产过程中的一个奇怪的错误。语句执行失败
if v_cursor%isopen then
close v_cursor; -- here was an error
end if;
After some digging into I discovered that problem was in subprogram that opened this cursor. I fixed bug by adding output parameter sys_refcursor in subprogram. To clarify situation consider following test code:
经过一番深入研究，我发现问题出在打开此游标的子程序中。我通过在子程序中添加输出参数 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;
If I try to run test_successeverything is OK, but on test_failI receive a message
如果我尝试运行test_success一切正常，但是test_fail我收到一条消息
ORA-01001: Invalid cursor
ORA-01001: 无效的游标
I cannot find any information about this. Can anyone explain why this code fails?
我找不到任何关于此的信息。谁能解释为什么这段代码失败？
Oracle version:
甲骨文版本：
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
回答by Alex Poole
This appears to be bug 7174888, or at least something closely related to it. The description for that is 'ORA-6504 raised when sys_refcursor passed to another procedure', but I can make that happen too if I change test_failto do a fetch:
这似乎是错误 7174888，或者至少是与其密切相关的错误。对此的描述是“当 sys_refcursor 传递给另一个过程时引发 ORA-6504”，但如果我更改test_fail为执行 fetch ，我也可以做到这一点：
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;
I get ORA-06504: PL/SQL: Return types of Result Set variables or query do not match.
我明白了ORA-06504: PL/SQL: Return types of Result Set variables or query do not match。
The workaround in the bug report cures both the fetch and close problem.
错误报告中的解决方法解决了获取和关闭问题。
Initialize the ref cursor to a non-NULL value at the highest level at which it will be accessed
将引用游标初始化为将被访问的最高级别的非 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;
回答by tbone
An interesting question! Just wanted to add a few things.
一个有趣的问题！只是想补充几点。
To me, the real issue is in depending on IS_OPEN to determine if a cursor is valid or not. Oracle can throw INVALID_CURSOR for many reasons, and it is possible to have an "open" cursor that is not valid. Seems reasonable to assume that an open cursor must be valid (and we can therefore fetch from it or do other operations, like a simple close), but this isn't necessarily the case.
对我来说，真正的问题在于依赖 IS_OPEN 来确定游标是否有效。Oracle 可以出于多种原因抛出 INVALID_CURSOR，并且可能有一个无效的“打开”游标。假设打开的游标必须有效（因此我们可以从中获取或执行其他操作，例如简单的关闭）似乎是合理的，但情况并非一定如此。
For example, you cannot use cursor variables in remote procedure calls (via dblinks). This same example, even using Alex's workaround, would fail if the open was called on 1 db instance and the fetch on another (if nested_test, any version, was defined on db_A and then called from db_B). The test for ISOPEN, however, would still return TRUE, but then trying to use the cursor (fetch) would fail.
例如，您不能在远程过程调用（通过 dblinks）中使用游标变量。同样的示例，即使使用 Alex 的解决方法，如果在 1 个 db 实例上调用 open 而在另一个实例上调用 fetch（如果nested_test，任何版本，在 db_A 上定义，然后从 db_B 调用），也会失败。但是，对 ISOPEN 的测试仍会返回 TRUE，但随后尝试使用游标 (fetch) 将失败。
INVALID_CURSOR can be raised for other reasons (like going beyond the max open cursors, or sometimes opening a cursor and waiting a while before trying to use it).
INVALID_CURSOR 可能因其他原因而引发（例如超出最大打开游标数，或者有时打开游标并等待一段时间后再尝试使用它）。
All that said, there is no "ISVALID" test that I know of. The best approach imo is to open, fetch and close cursors within the same program or subprogram. Creating a procedure whos responsibility is to just OPEN a cursor is a bit strange to me (but I'm sure there was some reason), and can cause hard to explain issues (like this one). If you must have another program open a cursor for you, then you might want to enclose the code that fetches and eventually closes the cursor in an anonymous block and catch the INVALID_CURSOR exception.
尽管如此，我所知道的没有“ISVALID”测试。imo 的最佳方法是在同一程序或子程序中打开、获取和关闭游标。创建一个只负责打开游标的过程对我来说有点奇怪（但我确定有一些原因），并且可能导致难以解释的问题（比如这个）。如果您必须让另一个程序为您打开游标，那么您可能希望将获取并最终关闭游标的代码包含在匿名块中并捕获 INVALID_CURSOR 异常。
Just my ramblings ;-)
只是我的胡言乱语;-)
