Calling Oracle proc from C# - must I pass in a cursor?

[Burt]

I want to create a simple procedure in Oracle that returns a resultset
(a select query). In all the examples I've seen, you have to pass in a
"ref cursor" OUT param into the proc from C#.

Is there a simpler way to do this? IWO can I not pass anything in, and
just declare the cursor in the proc? Or have no cursor, like with SQL
Server? Seems pointless to pass in an empty cursor param each time.

Thanks,

Burt

 

[Matt Noonan]

I want to create a simple procedure in Oracle that returns a resultset
(a select query). In all the examples I've seen, you have to pass in a
"ref cursor" OUT param into the proc from C#.

Is there a simpler way to do this? IWO can I not pass anything in, and
just declare the cursor in the proc? Or have no cursor, like with SQL
Server? Seems pointless to pass in an empty cursor param each time.

Unfortunately, with Oracle that's the way it is. If you use the EntLib, it
will add the cursor param for you, but you are correct that generally it is
up to you to do so.

 

[Burt]

Hi Matt,

Thanks for the reply. I am using the newest Enterprise Library, but
find I still have to pass in the cursor...do you have any links or
samples of how to get the cursor passed under the hood?

Thanks,

Burt

 

[Matt Noonan]

Hi Matt,

Thanks for the reply. I am using the newest Enterprise Library, but
find I still have to pass in the cursor...do you have any links or
samples of how to get the cursor passed under the hood?

Thanks,

Burt

ExecuteDataSet (line 172 of OracleDatabase.cs) looks like this:

public override DataSet ExecuteDataSet(DbCommand command)
{
PrepareCWRefCursor(command);
return base.ExecuteDataSet(command);
}

ExecuteReader(line 141 of OracleDatabase.cs) looks like this:

public override IDataReader ExecuteReader(DbCommand command)
{
PrepareCWRefCursor(command);
return new
OracleDataReaderWrapper((OracleDataReader)base.ExecuteReader(command));
}


And here is PrepareCWRefCursor:

private void PrepareCWRefCursor(DbCommand command)
{
if (command == null) throw new ArgumentNullException("command");

if (CommandType.StoredProcedure == command.CommandType)
{
// Check for ref. cursor in the command writer, if it does not exist,
add a know reference cursor out
// of "cur_OUT"
if (QueryProcedureNeedsCursorParameter(command))
{
AddParameter(command as OracleCommand, RefCursorName,
OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty,
DataRowVersion.Default, Convert.DBNull);
}
}
}


So perhaps you are not calling ExecuteDataSet or ExecuteReader? I'm afraid I
don't have any pure EntLib samples handy, so you'll have to show me your
code and we can work from there.

 

[Burt]

Thanks Matt. I was passing in a proc name, not a command object, to
ExecuteDataSet. When I changed it to a System.Data.Common.DbCommand
object, and renamed the cursor to cur_OUT, I got it working.

Burt