CODEWITHSUNDEEP
oracle-databasedelphidelphi-7odac
user1469630
user1469630

Reputation: 301

ORA-24338: Statement handle not executed

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.

Upvotes: 4

Views: 87712

Answers (5)

caged_raven_wings
caged_raven_wings

Reputation: 363

This error can happen if you are calling the procedure over a Dblink too.

When you return a REF CURSOR from a remote database (via a database link), the cursor itself points to a result set on the remote database. However, the local database might not be able to fetch rows from this remote cursor because the cursor handle is not recognized locally. hence the error.

Upvotes: 0

Michael Adams
Michael Adams

Reputation: 29

The first thing I would do is get rid of this code:

        Exception
        WHEN OTHERS THEN
        p_TOPIC := '';  
        END QTRAIN;

WHEN OTHERS tells Oracle to discard all error messages. Your stored procedure could be getting some other error and you would have no idea what happened. The ORA-24338 could be because another error occurred and the cursor was never opened.

Oracle PL/SQL has robust error handling if you don't use WHEN OTHERS. Oracle automatically rolls back the transaction and provides a complete error stack, to include the line number of the statement that caused the error.

Upvotes: 0

AlexSC
AlexSC

Reputation: 1933

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.

Upvotes: 7

Sam M
Sam M

Reputation: 4166

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.

Upvotes: 0

Anjan Biswas
Anjan Biswas

Reputation: 7932

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;

Upvotes: 0

Related Questions