oracle ORA-24338: 语句句柄未执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11991773/
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
ORA-24338: Statement handle not executed
提问by user1469630
i'm using Delphi 7, Oracle 10, and ODAC Components.
我正在使用 Delphi 7、Oracle 10 和 ODAC 组件。
From the LoadTrainResult method I'm calling a storedProc.
从 LoadTrainResult 方法中，我调用了一个存储过程。
procedure TfrmTrain.LoadTrainResult;
begin
StoredProc.StoredProcName := 'PTRAIN.QTRAIN';
StoredProc.Prepare;
try
StoredProc.ParamByName('P_COURSE').AsString := CurrentSearch.Course;
StoredProc.ParamByName('P_TOPIC').AsString := CurrentSearch.Topic;
StoredProc.ParamByName('P_EMP').AsString := CurrentSearch.Emp;
StoredProc.Open;
finally
StoredProc.Close;
end;
end;
The schema is
架构是
Create or replace PACKAGE TRAIN.pTRAIN IS
TYPE CursorType IS REF CURSOR;
PROCEDURE QTRAIN (p_CursorVar OUT CursorType, p_Course in VarChar2,
p_Topic in out VarChar2, p_emp in Varchar 2 );
END TRAIN.pTRAIN;
create or replace PACKAGE BODY TRAIN.pTRAIN IS
PROCEDURE QTRAIN (p_CursorVar OUT CursorType, p_Course in VarChar2,
p_Topic in out VarChar2, p_emp in Varchar 2 )
IS
BEGIN
if p_course is not null then
OPEN p_cursorvar for
select * from train.course
where course = p_Course;
elsif p_topic is not null then
OPEN p_cursorvar for
select *
from train.topic
where topic = p_topic;
end if;
Exception
WHEN OTHERS THEN
p_TOPIC := '';
END QTRAIN;
END TRAIN.pTRAIN;
When i'm compiling the package i didnt get any error. How ever when im running the application Im getting an error ORA-24338: Statement handle not executed. I debugged my application, I found out that the error happened at StoredProc.Prepare; not at StoredProc.ExecProc;
当我编译包时，我没有收到任何错误。当我运行应用程序时，我收到错误 ORA-24338：语句句柄未执行。我调试了我的应用程序，发现错误发生在 StoredProc.Prepare；不在 StoredProc.ExecProc;
I had read so many posts regarding the ORA-24338 and I was unable to find out what is wrong with my code.
我已经阅读了很多关于 ORA-24338 的帖子，但我无法找出我的代码有什么问题。
I found that when im adding an else condition into the storedproc i didnt get the error.
我发现当我在存储过程中添加一个 else 条件时，我没有收到错误消息。
The modified Proc is
修改后的 Proc 是
create or replace PACKAGE BODY TRAIN.pTRAIN IS
PROCEDURE QTRAIN (p_CursorVar OUT CursorType, p_Course in VarChar2,
p_Topic in out VarChar2, p_emp in Varchar 2 )
IS
BEGIN
if p_course is not null then
OPEN p_cursorvar for
select * from train.course
where course = p_Course;
elsif p_topic is not null then
OPEN p_cursorvar for
select * from train.topic
where topic = p_topic
else
OPEN p_cursorvar for
select * from emp whhere empid = p_emp;
end if;
Exception
WHEN OTHERS THEN
p_TOPIC := '';
END QTRAIN;
END TRAIN.pTRAIN;
Actually I dont want else condition. Is there is any way to get rid off the error.
其实我不想要其他条件。有什么办法可以摆脱错误。
回答by AlexSC
It seems to me that the problem is: in the first version of your stored procedure it's possible to have no resultsets to return, but in the second one you provided a resultset in the last else.
在我看来，问题是：在您的存储过程的第一个版本中，可能没有要返回的结果集，但在第二个版本中，您在最后一个 else 中提供了一个结果集。
My suspition is even stronger when we understand what the ORA-24338 means by taking a look at the full error report:
当我们通过查看完整的错误报告了解 ORA-24338 的含义时，我的怀疑更加强烈：
Error: ORA-24338
Text: statement handle not executed
---------------------------------------------------------------------------
Cause: A fetch was attempted before executing a statement handle.
Action: Execute a statement and then fetch the data.
A fetch was attempted, but in certain cases, there wasn′t any resultset to be fetched, until you provided it with the last else.
尝试 fetch，但在某些情况下，没有任何结果集要获取，直到您将最后一个 else 提供给它。
Your stored procedure returns a cursor by an output parameter, so you alwayshave to open that cursor. In the first version of your code, you didn't.
您的存储过程通过输出参数返回一个游标，因此您必须始终打开该游标。在您的代码的第一个版本中，您没有。
回答by AnBisw
I don't see any issues with the DB package PTRAINas such (schema is TRAIN), however, the calling application (Delphi 7?) needs to know how to use the cursor. Also, try using Dynamic SQL while opening the cursors like-
我没有看到 DB 包有任何问题PTRAIN（架构是TRAIN），但是，调用应用程序（Delphi 7？）需要知道如何使用cursor. 另外，在打开游标时尝试使用动态 SQL，例如 -
OPEN p_cursorvar for
'select * from train.course
where course = :p_Course' USING p_course;
And
和
OPEN p_cursorvar for
'select *
from train.topic
where topic = :p_topic' USING p_topic;
回答by Sam M
DevArt had a bug fix on August 2, 2007 that says "Bug with executing prepared stored procedures with REF cursor parameters fixed". I don't have any additional details other than what they provided in that statement in their bug fix list. It's possible that your version of ODAC might have that bug in it. ODAC is currently at version 8.2.8.
DevArt 于 2007 年 8 月 2 日修复了一个错误，指出“修复了带有 REF 游标参数的执行准备好的存储过程的错误”。除了他们在错误修复列表中的声明中提供的内容之外，我没有任何其他详细信息。您的 ODAC 版本可能存在该错误。ODAC 当前版本为 8.2.8。
