Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> ORA-24388 problem *Sometimes* - please advice

ORA-24388 problem *Sometimes* - please advice

From: J.Bijleveld <unavailable_at_microsoft.com>
Date: Wed, 25 Feb 2004 09:57:21 +0100
Message-ID: <403c646e$0$570$e4fe514c@news.xs4all.nl>

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()

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 Received on Wed Feb 25 2004 - 02:57:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US