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

does it work in Oracle?

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

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.

Ok. I will have a look through that lot later.

Check this and this too

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

Can you post the code of the procedure f1000_contributor_details.new_contributor?

Here is the explanation of the error:

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?

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.

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.

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

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