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;
/