Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2003
    Posts
    20

    Unanswered: ORA-24338: statement handle not executed

    Hi everyone,

    I currently have a stored procedure that takes two input parameters and one REF CURSOR output parameter which I am calling from ASP. Depending upon the values of the two input parameters a REF CURSOR can either be returned or nor returned. I.e.


    IF param1 = 2

    /* do something */

    ELSE IF param2 = 4

    /* Open REF CURSOR for return parameter */
    OPEN curs FOR SELECT * FROM temp;

    END IF;


    When a REF CURSOR is returned the ASP code is fine and I can display all the records it has within it. The trouble starts when param1 is 2 and therefore no REF CURSOR is returned. At this point I am greeted with the error:


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


    Does anyone know how I can deal with this situation. A REF CURSOR must only be returned under a specific condition (i.e. param2 = 4). Thanks.


    Regards,

    John
    Last edited by johnstv3; 03-11-03 at 11:25.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-24338: statement handle not executed

    Your ASP program needs to take into account whether the cursor will have been opened or not (i.e. what value of param2 was used). If the cursor was not opened, do not try to fetch from it.

  3. #3
    Join Date
    Mar 2003
    Posts
    20

    Re: ORA-24338: statement handle not executed

    Thanks Tony,

    So does that mean that, in the example I've given, only call the following code:


    rset = cmd.Execute();


    If I know something is going to be returned? Or is there something else that I should do? And how do I check at the ASP level whether the ref cursor will have been opened or not? Thanks in advance.


    Regards,

    John

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-24338: statement handle not executed

    Call the procedure:

    rset = cmd.Execute();

    That will execute it and maybe return a ref cursor.

    Now, in your ASP code where you intend to use that ref cursor, you need to have an IF statement:

    if xxx then
    (use the ref cursor)
    end if

    Now, since your ASP program called the PL/SQL procedure in the first place, presumably it knows what value it passed for param2? If so then the IF logic is

    if value_passed_as_param2 = 4 then
    (use the ref cursor)
    end if

    If somehow (I can't imagine how!) your ASP program doesn't know what value it passed as param2, then you would have to add an extra OUT parameter to the PL/SQL procedure - e.g. paramX OUT NUMBER

    Then in the PL/SQL procedure you would say:

    IF param1 = 2

    paramX := 0;
    /* do something */

    ELSE IF param2 = 4

    /* Open REF CURSOR for return parameter */
    OPEN curs FOR SELECT * FROM temp;
    paramX := 1;

    END IF;

    And in the ASP:

    if ParamX = 1 then
    (use the ref cursor)
    end if

  5. #5
    Join Date
    Mar 2003
    Posts
    20

    ORA-24338: statement handle not executed

    Thanks Tony,

    First of all I very much appreciate the help you're giving me. The error:


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


    actually occurs at the exact following line:


    rset = cmd.Execute();


    This is what has confused me? I would have thought that even if no REF CURSOR was returned (i.e. the "curs" REF CURSOR isn't initialised with the SELECT statement) the above line would still execute but this doesn't seem to be the case. How could I find out at this point if a NULL REF CURSOR was returned?

    I'm using Oracle9i so is this the reason why an error is being thrown? Does the Oracle9i OLEDB drivers have any faults that you know of? Have you experienced anything similar with ASP/Oracle? Once again, your help is much appreciated.


    Regards,

    John

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-24338: statement handle not executed

    I see - I don't understand why that would happen, it should only be when you first USE the ref cursor that you might get a problem.

    Hopefully someone else round here has seen the problem before.

    As a work-around, what about opening the ref cursor on a dummy select when param2 = 4? e.g.

    ELSIF param2 = 4 THEN
    open curs for SELECT * FROM temp WHERE 1=0;
    END IF;

  7. #7
    Join Date
    Mar 2003
    Posts
    20

    ORA-24338: statement handle not executed

    I've actually already done that Tony. Just to satisfy myself I used the following SQL:


    ELSIF param2 = 4 THEN
    OPEN curs FOR SELECT * FROM temp WHERE a = 999999;
    END IF;


    As there is no data in the "a" field which has 999999 then really no results should be returned. This is in fact the case. But what's surprising here is that the ASP presented me with no errors at all. This then made me then think that even if the SQL returned no data at least a REF CURSOR was still being returned, or should I say, initialised. When param1 = 2 then the REF CURSOR isn't initialised. Would you agree on this?

    Oracle8i never gave such problems but Oracle9i seems to. This leads me to wonder if there is a bug in the Oracle OLEDB drivers or that, maybe, our drivers need updating? I wonder if other developers have come across the problem and that I'm not an isolated case?

    Thanks again for your help


    Regards,

    John

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-24338: statement handle not executed

    I did a search on asktom.oracle.com for ORA-24338, and someone called John had posted a question on this in January - was that you by any chance? Anyway, Tom's answer missed the point, same as I did initially, since he naturally assumed that the problem was in trying to ACCESS a ref cursor that had not been opened, rather than just RETURN it and not access it. Maybe you could add a comment there about what is happening and he will look into it further?

  9. #9
    Join Date
    Mar 2003
    Posts
    20

    ORA-24338: statement handle not executed

    That wasn't me to be honest although the error is virtually the same. But I will take your advice on board and post a comment. What exactly does this line mean by the way?


    OPEN curs FOR SELECT * FROM dual WHERE 1=0;


    I would like to thank you again for the kind help that you have given me.


    Regards,

    John
    Last edited by johnstv3; 03-12-03 at 07:43.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-24338: statement handle not executed

    Originally posted by johnstv3
    What exactly does this line mean by the way?

    OPEN curs FOR SELECT * FROM dual WHERE 1=0;
    John
    It is a dummy SELECT that is guaranteed to return no rows.

    DUAL is an Oracle system table that has exactly 1 row, intended for use in queries like SELECT SYSDATE FROM DUAL;

    WHERE 1=0 is a condition that is never true, so guarantees that no rows will be returned.

  11. #11
    Join Date
    Mar 2003
    Posts
    20

    ORA-24338: statement handle not executed

    Fantastic. Thanks for all the help that you have given me. Much appreciated.


    Regards,

    John

  12. #12
    Join Date
    Oct 2003
    Location
    Brussels
    Posts
    1

    Re: ORA-24338: statement handle not executed

    Hi John.

    I have the same problem with C++ and Oracle Client 9.2 : my C++ program calls an Oracle Procedure with as outgoing parameter a cursor reference and if the Oracle Procedure does not initialise this cursor (in the case it is not needed), I get an error from the oracle client. Th old version of the Oracle client (8.1.7) did not give any problem.

    I solved this problem by always initialising a cursor in my oracle procedure even though I do not need and use it afterwards.

    Did you finally find another solution ?

    Thanks in advance

    Best regards

    Benoit

  13. #13
    Join Date
    Mar 2003
    Posts
    20

    Re: ORA-24338: statement handle not executed

    Benoit,

    Quite recently I came across the same problem again using Java and the same solution worked (i.e. initialise a dummy ref cursor even though the Java code isn't going to use it). This is about the only solution I've come across.

    There is one other solution though. Don't create a stored procedure with an OUT ref cursor parameter if you know that it's not going to return anything but that really depends upon the nature of the procedure.

    Thanks for pointing out to me to solution to your code problem.


    Regards,

    John

  14. #14
    Join Date
    Sep 2008
    Posts
    2
    the rule is very easy - if you don't open the cursor inside the stored proc then don't execute it in your calling app

    you can create it and bind it in your calling app then execute the stored proc

    but then don't execute the returned cursor unless you actually opened it within your stored proc code

    my recommendation is to return another variable in the OUT parameter list of your stored proc ie. a v_cursor_opened_boolean = T/F (T if you opened the cursor and F if you didn't)

    Then in your external app after executing the stored proc, check if v_cursor_opened_boolean = T then execute the returned cursor variable otherwise don't

    hope helpful

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why are you replying to a 4+ year old post?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •