ORA-24338: Statement handle not executed

Question

i'm using Delphi 7, Oracle 10, and ODAC Components.

From the LoadTrainResult method I'm calling a storedProc.

    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;

I had read so many posts regarding the ORA-24338 and I was unable to find out what is wrong with my code.

I found that when im adding an else condition into the storedproc i didnt get the error.

The modified Proc is

    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.

It looks like you use the StoredProc only once, so, why do you bother to prepare it? just call .Open and this might resolve the issue also. — jachguate, Aug 17 '12 at 02:50
Could it be that the second example has `end if` but the first hasn't? — MRAB, Aug 16 '12 at 18:20
I removed StoredProc.Prepare then also I got ORA-24338 error — user1469630, Aug 17 '12 at 14:12

AlexSC · Answer 1 · 2015-06-23T18:04:05.743

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.

My suspition is even stronger when we understand what the ORA-24338 means by taking a look at the full error report:

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.

Your stored procedure returns a cursor by an output parameter, so you always have to open that cursor. In the first version of your code, you didn't.

Anjan Biswas · Answer 2 · 2012-08-16T23:04:30.223

0

I don't see any issues with the DB package PTRAIN as 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-

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;

edited Aug 16 '12 at 23:04

answered Aug 16 '12 at 22:13

Anjan Biswas

7,746
5
47
77

When executing the StoredProc.Prepare statement I'm getting ORA-24338: Statement handle not executed error. – user1469630 Aug 16 '12 at 22:38

score 0 · Answer 3 · answered Aug 17 '12 at 18:57

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.

ORA-24338: Statement handle not executed

3 Answers3