Sometimes ORA-24388 problem - 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 heckDatabaseCompatibiliteit()
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+IExecutionStep.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

[Frank van Bortel]

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:
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 heckDatabaseCompatibiliteit()
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+IExecutionStep.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

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]

> >

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