J
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.PortalDataException: 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(OciHandle
errorHandle, Int32 rc)
at System.Data.OracleClient.OciHandle.GetAttribute(ATTR attribute,
Int32& value, OciHandle errorHandle)
at System.Data.OracleClient.OracleDataReader.FillColumnInfo()
at System.Data.OracleClient.OracleDataReader..ctor(OracleConnection
connection, OciHandle statementHandle)
at System.Data.OracleClient.OracleParameterBinding.GetOutputValue(NativeBuffer
parameterBuffer, OracleConnection connection, Boolean needCLSType)
at System.Data.OracleClient.OracleParameterBinding.PostExecute(NativeBuffer
parameterBuffer, OracleConnection connection)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean isReader, Boolean
needRowid, OciHandle& rowidDescriptor, ArrayList&
refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, ArrayList&
refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.OracleClient.OracleCommand.ExecuteReader()
at System.Data.OracleClient.OracleCommand.System.Data.IDbCommand.ExecuteReader()
at AnalyzeIT.Web.PortalFramework.Data.SysteemDB.GetDatabaseVersie()
--- End of inner exception stack trace ---
at AnalyzeIT.Web.PortalFramework.Data.SysteemDB.GetDatabaseVersie()
at AnalyzeIT.Web.PortalFramework.Common.DefaultPage.CheckDatabaseCompatibiliteit()
at AnalyzeIT.Web.PortalFramework.Common.DefaultPage.Page_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.HttpApplication+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep 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
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.PortalDataException: 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(OciHandle
errorHandle, Int32 rc)
at System.Data.OracleClient.OciHandle.GetAttribute(ATTR attribute,
Int32& value, OciHandle errorHandle)
at System.Data.OracleClient.OracleDataReader.FillColumnInfo()
at System.Data.OracleClient.OracleDataReader..ctor(OracleConnection
connection, OciHandle statementHandle)
at System.Data.OracleClient.OracleParameterBinding.GetOutputValue(NativeBuffer
parameterBuffer, OracleConnection connection, Boolean needCLSType)
at System.Data.OracleClient.OracleParameterBinding.PostExecute(NativeBuffer
parameterBuffer, OracleConnection connection)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean isReader, Boolean
needRowid, OciHandle& rowidDescriptor, ArrayList&
refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, ArrayList&
refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.OracleClient.OracleCommand.ExecuteReader()
at System.Data.OracleClient.OracleCommand.System.Data.IDbCommand.ExecuteReader()
at AnalyzeIT.Web.PortalFramework.Data.SysteemDB.GetDatabaseVersie()
--- End of inner exception stack trace ---
at AnalyzeIT.Web.PortalFramework.Data.SysteemDB.GetDatabaseVersie()
at AnalyzeIT.Web.PortalFramework.Common.DefaultPage.CheckDatabaseCompatibiliteit()
at AnalyzeIT.Web.PortalFramework.Common.DefaultPage.Page_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.HttpApplication+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep 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