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.

Answer 1

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.