ASP.NET Oracle problem - please help

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
 
M

Miha Markic [MVP C#]

Hi,

Sounds to me like an oracle problem.
You might try upgrading to 8.1.7....

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

J.Bijleveld said:
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 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.CheckDatabaseCompatibilitei
t()
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+IExecutionSte
p.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
Click to expand...
 
K

Kevin Yu [MSFT]

Thanks for Miha's quick response.

Hi J. Bijleveld,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that your ASP.NET application randomly
throws an exception. If there is any misunderstanding, please feel free to
let me know.

Based on my research, this exception is often caused by Oracle client.
Calling a stored procedure that return a null reference cursor will fail
with ORA-24338.

The problem is that when your code tries to get the result set, it does
that by executing the stored procedure and throwing a dummy value at any
arguments. Since those dummy arguments are likely not to result in an
actual result set, an error is generated.

This issue is fixed in newer version of Oracle client. Please try to
upgrade to 8.1.7 or later as Miha suggested.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
K

Kevin Yu [MSFT]

Hi J. Bijleveld,

I'd like to know if the issue has been resoved yet. Is there anything that
I can help on this? I'm still monitoring on it. If you have any further
questions about this issue, please reply to the post in newsgroup.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
J

JB

Hello Kevin,

thanks for monitoring my thread, I did not yet find a solution to this
problem.

I did get a suggestion of someone telling me it might have to do with 2
stored procedures (say 1 and 2). If the first returns data (using a
cursor) and that data is used for the second stored procedures. If they
are called independant of eachother, then they might be executed
out-of-order. In that case the 2nd one cannot yet use the data of the
first one, which it might expect to be available.

This sound logical to me but I do not yet see how I could have created
such a situation since:
- none of our stored procedures uses cursors for loops or things (only
like in the procedure I posted)
- none of our stored procedures calls another stored procedure, they all
work independantly.
- In C# we do combine calls of stored procedures (first call one, then
the other) but I don't see how this could get out of order. I would
expect that if the 1st one could not be executed, it would block the
flow (or at least raise an exception) and the 2nd would not be executed
before the 1st one is.

Can you perhaps help me out on this one? In which scenario's would it be
possible for this behaviour to be exposed?

Is there some Oracle tool I could use to see what's going on? (Please
remember I'm no Oracle Guru :) ) In the eventlog I only see the
exception that occurs when the ORA-24338 error occurs. I have a feeling
that BEFORE that exception, something at another location must have gone
wrong, of which I do not get a message.

Best regards,
Jeroen
 
K

Kevin Yu [MSFT]

Hi Jeroen,

Have you tried my suggestion to upgrade Oracle client from 8.1.6 to 8.1.7?

There is a tool named Oracle Trace Manager I found on the web. It might be
the tool you want. We can use it to collect a wide variety of data, such as
performance statistics, diagnostics data, system resource usage, and
business transaction details. Here is an article about Oracle Trace Manager.

http://www-rohan.sdsu.edu/doc/oracle/oem140/A53696_01/intr.htm

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
J

JB

Hi Kevin,

Thanks for responding, I did a double-check on the version of Oracle and
it is version 8.1.7.0.0 instead of 8.1.6 which I mentioned in the
previous posting.

In other postings I read that the latest version is 8.1.7.4 but I don't
know where to get the patches. Furthermore, the productiondatabase is
also version 8.1.7.0.0 and I'm not the one to decide that it should be
updated so that's out of my control. Ofcourse I can strongly advice to
do so if it should be required to resolve this issue.

The Oracle Trace utility is unknown to me I'll check it out to see if
it's usefull for me. As I read, it works with instrumented applications,
so first I would have to add instrumentation calls to the application.
I'll have to check out how to do so and how much work it involves.

Thanks for your information, if you have any further suggestions or
directions to look for answers, please let me know. All information is
greatly appreciated.

Best regards,
Jeroen Bijleveld
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MS Oracle .NET Provider and REF CURSOR Error 0
Issue with Managed Oracle Provider in .NET 3
MIcrosoft ODP 1
Problem when accessing Oracle Procedure with varchar2 1
system.data.oracleclient error 3
Subprocedure for Oracle's SET DEFINE OFF command 3
Sometimes we get these errors on ASP.NET project. 1
Oracle Lite 9i connection (newbie) 2

Top