1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-24338: statement handle not executed

Discussion in 'SQL PL/SQL' started by straygrey, Apr 7, 2009.

  1. straygrey

    straygrey Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have a stored procedure which has a REF cursor output parameter depending on whether a condition is met or not. This REF cursor is then called from my PHP code. The procedure also has one IN parameter.

    The scenarios are something like this:
    Code (Text):

    IF condition1 is true

    /* do something */

    ELSE

    /* Open REF CURSOR for return OUT parameter */

    END IF;
     
    Now when the condition is goes into the else statement and the REF CURSOR is returned my PHP code works fine and I can display all the records from the cursor. But the problem is that when the IF condition evaluates to true and no REF CURSOR is returned then I get the error below:

    OraOLEDB error '80004005'
    ORA-24338: statement handle not executed


    Can someone suggest how to workaround this issue. I cannot return a REF cursor when the IF condition is true.

    Thanks
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Well.... in case when condition 1 is true, you are not opening any ref cursor,but you are calling it from your PHP code.This is not allowed.

    As a workaround, can your PHP code predict the outcome of condition1 ? If yes, then call the ref cursor based on the outcome of condition 1.
     
  3. straygrey

    straygrey Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    hi simply_dba thanks for your reply. No actually i can't predict in my code whether a REF cursor would be returned or not. However I noticed one thing that the error actually occurs when I try to execute the result set in php. So it doesn't seem to be a problem with oracle not returning a REF cursor, but I might be wrong.

    I am considering modifying th procedure to open a dummy REF cursor so that one Cursor is always returned. Let me know your thoughts on this.
     
  4. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Well......
    try it
     
  5. straygrey

    straygrey Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    hi i finally got the code to work. So if someone else faces the same problem the issue is very simple, if you are not opening a cursor inside the stored procedure then don't try to execute it in your calling application code.

    What I did was, I used an additional return variable in the OUT parameter list of my stored procedure. I used a variable v_cursor_open which returns 1/0, depending on whethermy REF cursor opening condition was met or not.

    Now in my applicatopn code after executing the stored procedure I checked the value of v_cursor_open. When it was 1, then only I am executing the returned cursor variable.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can also try the DUMMY ref cursor as suggested by yourself.


    Code (Text):

    IF condition1 is true

    /*  Open Dummy REF CURSOR for return OUT parameter */

    ELSE

    /* Open REF CURSOR for return OUT parameter */

    END IF;
     
    Dummy REF CURSOR could be like

    SELECT NULL , NULL , NULL,....from dual;

    (Same number of null list as in actual ref cursor)
     
  7. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    As I said before .........