ORA-24388 problem *Sometimes* - please advice

[J.Bijleveld]

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:
ExceptionManager.MachineName: TEST-DIG
ExceptionManager.TimeStamp: 23-2-2004 16:38:25
ExceptionManager.FullName: Microsoft.ApplicationBlocks.ExceptionManagement,
Version=1.0.1376.20570, Culture=neutral, PublicKeyToken=9f1cd949e9897e4a
ExceptionManager.AppDomainName:
/LM/W3SVC/1818675049/Root-1-127219909862343750
ExceptionManager.ThreadIdentity:
ExceptionManager.WindowsIdentity: NT AUTHORITY\NETWORK SERVICE

Exception Information
System.Web.HttpUnhandledException: Exception of type
System.Web.HttpUnhandledException was thrown.
---> AnalyzeIT.Web.PortalFramework.Common.PortalDataExc eption: PF DB: Het
opvragen van de databaseversie is mislukt. --->
System.Data.OracleClient.OracleException: ORA-24338: Statement handle not
executed
at System.Data.OracleClient.OracleException.Check(Oci Handle errorHandle,
Int32 rc)
at System.Data.OracleClient.OciHandle.GetAttribute(AT TR attribute, Int32&
value, OciHandle errorHandle)
at System.Data.OracleClient.OracleDataReader.FillColu mnInfo()
at System.Data.OracleClient.OracleDataReader..ctor(Or acleConnection
connection, OciHandle statementHandle)
at
System.Data.OracleClient.OracleParameterBinding.Ge tOutputValue(NativeBuffer
parameterBuffer, OracleConnection connection, Boolean needCLSType)
at
System.Data.OracleClient.OracleParameterBinding.Po stExecute(NativeBuffer
parameterBuffer, OracleConnection connection)
at System.Data.OracleClient.OracleCommand.Execute(Oci Handle
statementHandle, CommandBehavior behavior, Boolean isReader, Boolean
needRowid, OciHandle& rowidDescriptor, ArrayList&
refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(Oci Handle
statementHandle, CommandBehavior behavior, ArrayList&
refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteRead er(CommandBehavior
behavior)
at System.Data.OracleClient.OracleCommand.ExecuteRead er()
at
System.Data.OracleClient.OracleCommand.System.Data .IDbCommand.ExecuteReader(
)
at AnalyzeIT.Web.PortalFramework.Data.SysteemDB.GetDa tabaseVersie()
--- End of inner exception stack trace ---
at AnalyzeIT.Web.PortalFramework.Data.SysteemDB.GetDa tabaseVersie()
at
AnalyzeIT.Web.PortalFramework.Common.DefaultPage.C heckDatabaseCompatibilitei
t()
at AnalyzeIT.Web.PortalFramework.Common.DefaultPage.P age_Load(Object
sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain()
--- End of inner exception stack trace ---
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain()
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at
System.Web.CallHandlerExecutionStep.System.Web.Htt pApplication+IExecutionSte
p.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean&
completedSynchronously)

The only statement executed by our code is calling
SysteemDB.GetDatabaseVersie (in the namespace
AnalyzeIT.Web.PortalFramework.Data)
The code for it is:
CREATE OR REPLACE PACKAGE PkgSystem AS
TYPE CRSR IS REF CURSOR;

PROCEDURE GetDatabaseVersie(ERROR OUT NUMBER,CUR OUT CRSR);
END PkgSystem;
/

CREATE OR REPLACE PACKAGE BODY PkgSystem AS
PROCEDURE GetDatabaseVersie(ERROR OUT NUMBER,CUR OUT CRSR) IS
BEGIN
ERROR := 0;
OPEN CUR FOR
SELECT SI_Waarde as DatabaseVersie
FROM Systeeminformatie
WHERE SI_NAAM='DATABASEVERSIE';
EXCEPTION
WHEN OTHERS THEN
ERROR := SQLCODE;
END GetDatabaseVersie;
END PkgSystem;
/


This will execute a stored procedure in the package PGKSystem which has the
following implementation:

public string GetDatabaseVersie()
{
cm = DatabaseFactory.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.Connection = this.InternalConnection;
cm.CommandText = "PkgPortaalBeheer.GetDatabaseVersie";

OracleParameter pa = new OracleParameter();
pa.OracleType = OracleType.Cursor;
pa.ParameterName = "CUR";
pa.Direction = ParameterDirection.Output;
cm.Parameters.Add(pa);

IDbDataParameter param = new OracleParameter();
param.ParameterName = "ERROR";
param.DbType = DbType.Decimal;
param.Direction = ParameterDirection.Output;
cm.Parameters.Add(param);
string DatabaseVersion = "";
try
{
this.Connection.Open();
IDataReader dr = cm.ExecuteReader();

// Get the database version
while(dr.Read())
{

try
{
DatabaseVersion = dr["DatabaseVersion"] as string;
}
catch(Exception exc)
{
throw new PortalDataException("PF DB: Failed to get DB version",exc);
}
}
}
catch(Exception exc)
{
throw new PortalDataException("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