Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Posts
    20

    Unanswered: ORA-24338: statement handle not executed

    Hi Everyone,

    I keep on getting this error when I use ASP to execute an Oracle 9i stored procedure that returns not only data but also a REF CURSOR:


    OraOLEDB error '80004005'

    ORA-24338: statement handle not executed


    Does anyone know what the error refers to?


    Thanks,

    John

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    does it work in Oracle?

    Can you post the code used to execute it or explain further

  3. #3
    Join Date
    Mar 2003
    Posts
    20
    Thanks for replying. Here is the code below:


    var cmd2 = Server.CreateObject("ADODB.Command");
    cmd2.ActiveConnection = Application("bmcmaindb");
    var rset3 = Server.CreateObject("ADODB.RecordSet");

    var usr_id = cmd2.CreateParameter("usr_id", adNumeric, adParamInput, 1, cd_usr_id);
    var email_freq = cmd2.CreateParameter("email_freq", adNumeric, adParamInput, 1, cd_email_freq);
    var cstart_date = cmd2.CreateParameter("cstart_date", adVarChar, adParamInput, 11, cd_con_start_date);
    var cend_date = cmd2.CreateParameter("cend_date", adVarChar, adParamInput, 11, cd_con_finish_date);
    var status = cmd2.CreateParameter("status", adVarChar, adParamInput, 12, cd_status);
    var f_spc_id = cmd2.CreateParameter("f_spc_id", adNumeric, adParamInput, 1, cd_faculty_spc_id);
    var s_spc_id = cmd2.CreateParameter("s_spc_id", adNumeric, adParamInput, 1, cd_section_spc_id);
    var sstart_date = cmd2.CreateParameter("sstart_date", adVarChar, adParamInput, 11, cd_sab_start_date);
    var send_date = cmd2.CreateParameter("send_date", adVarChar, adParamInput, 11, cd_sab_finish_date);
    var notes = cmd2.CreateParameter("notes", adLongVarChar, adParamInput, 10000, cd_notes);
    var emailvol = cmd2.CreateParameter("emailvol", adNumeric, adParamInput, 1, cd_email_volume);
    var transtype = cmd2.CreateParameter("transtype", adNumeric, adParamInput, 1, cd_transaction_type);
    var status_code = cmd2.CreateParameter("status_code", adNumeric, adParamOutput, 1, cd_status_code);


    cmd2.CommandType = adCmdText
    cmd2.CommandText = "{ CALL f1000_contributor_details.new_contributor(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";

    cmd2.Properties("SPPrmsLOB") = true;
    cmd2.Properties("PLSQLRSet") = true;
    rset3 = cmd2.Execute();
    cmd2.Properties("PLSQLRSet") = false;
    cmd2.Properties("SPPrmsLOB") = false;

    cd_status_code = cmd2.Parameters("status_code").Value;

    rset3.Close();
    rset3 = null;
    cmd2 = null;


    I am currently using Oracle 9i client but i'm not sure what version of MDAC? Could it be due to the fact that 9i client has problems or that the version of MDAC may be incorrect? Thanks in advance.

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Ok. I will have a look through that lot later.

    Check this and this too
    Make sure you start the listener control on the server. This could be one of the problems. Some times it happens that the name of the server host in the tnsnames.ora file on the client side is not recognized, substitute this with the IP address of the server. Make sure you have the correct service name of the database in the tnsnames.ora file on the server as well as the client.
    Make sure you are trying to connect to the databse this way.
    sqlplus@servicename usrname/password.
    I hope this helps you.

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Also, you may get more luck in the oracle forum - I think this is likely to be a problem with Oracle rather than ASP (I may be wrong). I will move the thread if you want

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Can you post the code of the procedure f1000_contributor_details.new_contributor?

    Here is the explanation of the error:

    ORA-24338 statement handle not executed

    Cause: A fetch or describe was attempted before executing a statement handle.

    Action: Execute a statement and then fetch or describe the data.

  7. #7
    Join Date
    Mar 2003
    Posts
    20

    ORA-24338: statement handle not executed

    Thanks for the replies. The code is actually within this thread further up the web page. I felt this was an ASP problem because one of the db guys over here said that the "OraOLEDB error '80004005'" related to something at the ASP level. He said to check the ASP code first although my gut instinct tells me that Oracle may be playing up?

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

    Re: ORA-24338: statement handle not executed

    No, I was asking to see the PL/SQL code of the procedure named f1000_contributor_details.new_contributor. You have shown the ASP code that calls the procedure, but not the procedure itself.

    The error you are getting is being raised by Oracle, it is:

    ORA-24338: statement handle not executed

    Without knowing what f1000_contributor_details.new_contributor actually does, it is not possible to debug.

  9. #9
    Join Date
    Mar 2003
    Posts
    20

    Re: ORA-24338: statement handle not executed

    Thanks for replying Tony. The procedure is just too long in this case and I have removed the bulk of the code. I have also ommited the exceptions code for compactness.

    I would stress though that under both pi_transaction_type 1 and 2 there are no calls being made to a REF CURSOR and are merely data manipulation code. Such a call to the REF CURSOR is only made when pi_transaction_type is neither 1 nor 2 as can be seen at the bottom of the IF-ELSE statement.


    PROCEDURE new_contributor ( pi_usr_id IN contrib.cont_usr_id%TYPE,
    pi_email_freq IN contrib.cont_email_freq%TYPE,
    pi_con_start_date IN VARCHAR2,
    pi_con_finish_date IN VARCHAR2,
    pi_status IN contrib.cont_status%TYPE,
    pi_faculty_spc_id IN contrib.cont_faculty_spc_id%TYPE,
    pi_section_spc_id IN contrib.cont_section_spc_id%TYPE,
    pi_sab_start_date IN VARCHAR2,
    pi_sab_finish_date IN VARCHAR2,
    pi_notes IN contrib.cont_notes%TYPE,
    pi_email_volume IN contrib.cont_email_volume%TYPE,
    pi_transaction_type IN NUMBER,
    po_contr_record OUT ref_contributor_details,
    po_status_code OUT NUMBER )

    IS

    /* Variable declaration */

    BEGIN

    IF( pi_transaction_type = 1 ) THEN

    /* do something */

    ELSIF( pi_transaction_type = 2 ) THEN

    /* do something else*/

    ELSE

    /* Get The Contributors Details And Pass Back As A Ref Cursor To Calling Environment */

    OPEN po_contr_record FOR SELECT * FROM contrib WHERE cont_usr_id = pi_usr_id;

    END IF;

    COMMIT;

    END;


    In the case of my ASP page pi_transaction_type is 2 so no REF CURSOR is returned I believe. Should settings in the Oracle 9i client need changing to get it working? What I can say is that our previous 8i client had no problems with this procedure. Thanks in advance.

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

    Re: ORA-24338: statement handle not executed

    OK, so if you call this procedure with pi_transaction_type set to 1 or 2 then the REF CURSOR is not opened. If your ASP then tries to fetch a record from the REF CURSOR you will get the ORA-24338 error - looks like that's exactly what is happening.

    Here is a simple example run in SQL Plus:

    create or replace package pkg is
    type t_rc is ref cursor;
    procedure get( rc out t_rc );
    end;
    /

    create or replace package body pkg is
    procedure get( rc out t_rc )
    is
    begin
    null; /* Does not open the cursor */
    end;
    end;
    /

    SQL> REM Declare a ref cursor variable
    SQL> var rc refcursor
    SQL> REM Run the procedure
    SQL> exec pkg.get(:rc)

    PL/SQL procedure successfully completed.

    SQL> REM Try to access the ref cursor
    SQL> print :rc
    ERROR:
    ORA-24338: statement handle not executed


    Error printing variable "rc"



    ORA-24338: statement handle not executed

  11. #11
    Join Date
    Mar 2003
    Posts
    20

    Re: ORA-24338: statement handle not executed

    Your help is very much appreciated Tony. But then why did it work on 8i and not on 9i? Strange?

Posting Permissions

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