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:
> 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
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.

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