Sometimes ORA-24388 problem - please advice

**Frank van Bortel** · Jun 27 '08, 06:01 PM

Re: Sometimes ORA-24388 problem - please advice

J.Bijleveld wrote:

Hello colleagues,
>
At this moment we have a real big problem using a .NET application
with an Oracle database (v8.1.6). I hope someone has encountered this
problem before and is able to help me with it.
>
The problem is that we *Sometimes* get the ORA-24338 error when
executing a query. This problem occurs random, sometimes twice a day,
sometimes not at all. In the eventlog we get an exception like this
one (stacktrace):
>
>
Additonal Info:
ExceptionManage r.MachineName: TEST-DIG
ExceptionManage r.TimeStamp: 23-2-2004 16:38:25
ExceptionManage r.FullName:
Microsoft.Appli cationBlocks.Ex ceptionManageme nt,
Version=1.0.137 6.20570, Culture=neutral ,
PublicKeyToken= 9f1cd949e9897e4 a
ExceptionManage r.AppDomainName :
/LM/W3SVC/1818675049/Root-1-127219909862343 750
ExceptionManage r.ThreadIdentit y:
ExceptionManage r.WindowsIdenti ty: NT AUTHORITY\NETWO RK SERVICE
>
Exception Information
System.Web.Http UnhandledExcept ion: Exception of type
System.Web.Http UnhandledExcept ion was thrown.
---AnalyzeIT.Web.P ortalFramework. Common.PortalDa taException: PF DB:
Het opvragen van de databaseversie is mislukt. --->
System.Data.Ora cleClient.Oracl eException: ORA-24338: Statement handle
not executed
at System.Data.Ora cleClient.Oracl eException.Chec k(OciHandle
errorHandle, Int32 rc)
at System.Data.Ora cleClient.OciHa ndle.GetAttribu te(ATTR attribute,
Int32& value, OciHandle errorHandle)
at System.Data.Ora cleClient.Oracl eDataReader.Fil lColumnInfo()
at System.Data.Ora cleClient.Oracl eDataReader..ct or(OracleConnec tion
connection, OciHandle statementHandle )
at System.Data.Ora cleClient.Oracl eParameterBindi ng.GetOutputVal ue(NativeBuffer
parameterBuffer , OracleConnectio n connection, Boolean needCLSType)
at System.Data.Ora cleClient.Oracl eParameterBindi ng.PostExecute( NativeBuffer
parameterBuffer , OracleConnectio n connection)
at System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle
statementHandle , CommandBehavior behavior, Boolean isReader, Boolean
needRowid, OciHandle& rowidDescriptor , ArrayList&
refCursorParame terOrdinals)
at System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle
statementHandle , CommandBehavior behavior, ArrayList&
refCursorParame terOrdinals)
at System.Data.Ora cleClient.Oracl eCommand.Execut eReader(Command Behavior
behavior)
at System.Data.Ora cleClient.Oracl eCommand.Execut eReader()
at System.Data.Ora cleClient.Oracl eCommand.System .Data.IDbComman d.ExecuteReader ()
at AnalyzeIT.Web.P ortalFramework. Data.SysteemDB. GetDatabaseVers ie()
--- End of inner exception stack trace ---
at AnalyzeIT.Web.P ortalFramework. Data.SysteemDB. GetDatabaseVers ie()
at AnalyzeIT.Web.P ortalFramework. Common.DefaultP age.CheckDataba seCompatibilite it()
at AnalyzeIT.Web.P ortalFramework. Common.DefaultP age.Page_Load(O bject
sender, EventArgs e)
at System.EventHan dler.Invoke(Obj ect sender, EventArgs e)
at System.Web.UI.C ontrol.OnLoad(E ventArgs e)
at System.Web.UI.C ontrol.LoadRecu rsive()
at System.Web.UI.P age.ProcessRequ estMain()
--- End of inner exception stack trace ---
at System.Web.UI.P age.HandleError (Exception e)
at System.Web.UI.P age.ProcessRequ estMain()
at System.Web.UI.P age.ProcessRequ est()
at System.Web.UI.P age.ProcessRequ est(HttpContext context)
at System.Web.Call HandlerExecutio nStep.System.We b.HttpApplicati on+IExecutionSt ep.Execute()
at System.Web.Http Application.Exe cuteStep(IExecu tionStep step,
Boolean& completedSynchr onously)
>
>
>
The only statement executed by our code is calling
SysteemDB.GetDa tabaseVersie (in the namespace
AnalyzeIT.Web.P ortalFramework. Data)
The code for it is:
CREATE OR REPLACE PACKAGE PkgSystem AS
TYPE CRSR IS REF CURSOR;
>
PROCEDURE GetDatabaseVers ie(ERROR OUT NUMBER,CUR OUT CRSR);
END PkgSystem;
/
>
CREATE OR REPLACE PACKAGE BODY PkgSystem AS
PROCEDURE GetDatabaseVers ie(ERROR OUT NUMBER,CUR OUT CRSR) IS
BEGIN
ERROR := 0;
OPEN CUR FOR
SELECT SI_Waarde as DatabaseVersie
FROM Systeeminformat ie
WHERE SI_NAAM='DATABA SEVERSIE';
EXCEPTION
WHEN OTHERS THEN
ERROR := SQLCODE;
END GetDatabaseVers ie;
END PkgSystem;
/
>
>
>
>
This will execute a stored procedure in the package PGKSystem which
has the following implementation:
>
public string GetDatabaseVers ie()
{
cm = DatabaseFactory .CreateCommand( );
cm.CommandType = CommandType.Sto redProcedure;
cm.Connection = this.InternalCo nnection;
cm.CommandText = "PkgPortaalBehe er.GetDatabaseV ersie";
>
OracleParameter pa = new OracleParameter ();
pa.OracleType = OracleType.Curs or;
pa.ParameterNam e = "CUR";
pa.Direction = ParameterDirect ion.Output;
cm.Parameters.A dd(pa);
>
IDbDataParamete r param = new OracleParameter ();
param.Parameter Name = "ERROR";
param.DbType = DbType.Decimal;
param.Direction = ParameterDirect ion.Output;
cm.Parameters.A dd(param);
>
>
string DatabaseVersion = "";
try
{
this.Connection .Open();
IDataReader dr = cm.ExecuteReade r();
>
// Get the database version
while(dr.Read() )
{
>
try
{
DatabaseVersion = dr["DatabaseVersio n"] as string;
}
catch(Exception exc)
{
throw new PortalDataExcep tion("PF DB: Failed to get DB
version",exc);
}
}
}
catch(Exception exc)
{
throw new PortalDataExcep tion("PF DB: Failed to get DB
version",exc);
}
finally
{
this.Connection .Close();
}
return DatabaseVersion ;
}
>
>
This code was a little modified to make it more clear, but the
statements are intact.
>
To my best knowledge, this is how things work. Also, this code
executes fine in 99% of the time. However, if something goes wrong and
the ORA-24388 error occurs, Oracle cannot be reached by the website
for about 5 minutes after the last ORA-24388 error.
>
So: if the error occurs, and we try again 4 minutes later, then it
seems to reset the counter and will from then on be unavailble for 5
minutes. So it will be unavailable for 9 minutes in total. (and so on)
>
I have no clue why it's 5 minutes and why the database is unavailble
for other commands as well in that time.
>
I really hope someone is willing to help me out here, I've been
struggling with this problem for about 2 months now and we were not
able to resolve this until now!
>
Any advice is highly appreciated!
>
J. Bijleveld
The Netherlands

Cannot comment on why the error would occur, as my knowledge of
..Net is very minimal.
No doubt you already knew this:
24338, 00000, "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.

Undoubtly, you also know your version is not a pleasant one - at least
upgrade, and patch to 8.1.7.4.

As to the 5 minutes stuff - you seem to do something with Portal;
if this would be Oracle Portal, there's a time-out on IP-address
when all login attempts (3) have failed. IIRC, that is 5 minutes by
default, but increases. Your login failure count is reset every 24hrs.

--

Regards,
Frank van Bortel

**J.Bijleveld** · Jun 27 '08, 06:01 PM

Re: Sometimes ORA-24388 problem - please advice

J. Bijleveld
The Netherlands

Cannot comment on why the error would occur, as my knowledge of
.Net is very minimal.
No doubt you already knew this:
24338, 00000, "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.
>
Undoubtly, you also know your version is not a pleasant one - at least
upgrade, and patch to 8.1.7.4.
>
As to the 5 minutes stuff - you seem to do something with Portal;
if this would be Oracle Portal, there's a time-out on IP-address
when all login attempts (3) have failed. IIRC, that is 5 minutes by
default, but increases. Your login failure count is reset every 24hrs.

Hi Frank,

Thanks for your answer to my question, I did indeed find the error
info you added below but I couldn't figure out in what why I would
have to modify my stored procedure to make it work all of the time.
It says "Execute a statement and then fetch or describe the data". In
my opinion I already do so, please correct me if I'm wrong.

Thanks for your suggestion about the Oracle version, I'll make sure to
have our customer check the patch-level so it's 8.1.7.4, if not, I'll
send them the advice to check out that patch and apply it (if
possible).

About the Portal: that's not the Oracle Portal (I should have
mentioned that).
The application that uses Oracle is a framework to create portals.

Thanks again for your information,

Groeten,
Jeroen Bijleveld

Sometimes ORA-24388 problem - please advice

Sometimes ORA-24388 problem - please advice

Comment

Comment