Oracle cursors not releasing with OracleClient

  • Thread starter Thread starter ORi
  • Start date Start date
O

ORi

Hi all,

I'm having a problem with Oracle .net provider and Stored Procedures
returning REF CURSOR vars. I'm using OracleClient v1.0.1012.0 with
Framework 1.0 and when I fill a dataset with the stored procedure, the
cursor is not released and I always end up with a ORA-01000 error (too
much cursors opened).

It's very frustrating and I've observed that if I change the
OracleClient reference to the one from framework 1.1 it doesn't happen
and the cursors are released when closing the connection.
Unfortunately, I can't change the framework version I'm using so I'd
like to know if there's something I could do to fix this behaviour
with framework 1.0

Another strange thing I've noticed is that if I call the function (see
code) in a pageload event of an aspnet application I don't get the
ORA-01000 error but if I call it from the Console Application I can't.

I'll put some code here to reproduce the behaviour, any help will be
appreciated !!

Thx in advance,
ori

using System;
using System.Data.OracleClient;
using System.Data;

namespace ConsoleApplication2
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
[STAThread]
static void Main(string[] args)
{
Class1 c = new Class1();

for (int i=0; i<850; i++)
{
c.try();
}

}

public void prova()
{
try
{
OracleConnection conn = new OracleConnection(@"<connection string
here>");
conn.Open();

OracleCommand comm = new OracleCommand();
comm.Connection = conn;

comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.CommandText = "PKG.READ";
comm.Parameters.Add(new OracleParameter("P_CURSOR",
OracleType.Cursor, 38, ParameterDirection.Output, true, 38, 0, "",
System.Data.DataRowVersion.Current, null));


OracleDataAdapter adapter = new OracleDataAdapter(comm);
DataSet dset = new DataSet();
adapter.Fill(dset);

conn.Close();
}
catch (System.Exception ex)
{
//ORA-01000 Error when default 300 cursors are opened
}
}

public Class1()
{
}
}
}

Stored procedure

CREATE OR REPLACE PACKAGE PKG AS
TYPE etrcur IS REF CURSOR;

PROCEDURE READ
(
p_CURSOR OUT etrcur
);

END PKG;
/

CREATE OR REPLACE PACKAGE BODY PKG AS

PROCEDURE READ
(
p_CURSOR OUT etrcur
)
IS
BEGIN

OPEN p_cursor FOR
SELECT <ANY SELECT HERE>;

END SGM_READ;

END PKG;
/
 
Miha is correct, this is a provider bug. There is a QFE available for
this problem that you can obtain by calling PSS.

In general we recomend using the 1.1 version of this provider, it has
additional functionality and all of the QFE fixes that went into 1.0.
If this is not possible please make sure you have the latest QFEs for
this provider or you could look into Oracle's ODP.net or DataDirects
managed provider.

Thanks,
Angel
This post is provided AS IS and confers no rights, this email alias is
for posting purposes only, please do not respond to this email
directly.
 
Ok, thanks for everything I'll take a look at ODP.Net and also will
call for the QFE and give it a try.

As you said, the problem was solved if OracleClient 1.1 was used.
However, when I did that (I'm developing under Visual Studio .NET not
2003) I got a PInvoke exception when trying to connect to the Db with
OracleConnection.Open() method, I read somewhere that this was because
it was not possible to mix framework 1.0 and 1.1 in a VS Net project
or something similar. Is it right? The exact error I got was a
System.ExecutionEngineException and then said something about a
PInvoke error.

Thx for all,
ori
 
Back
Top