REF CURSOR returns no rows

[MW]

Dear All,

I am having a really strange problem with reading cursors from an Oracle 9i
database for a "particular" procedure. My DataReader does not contain any
rows after I execute the command object. I have tried

cmd.ExecuteReader

and

x = DataAdaptor(cmd)
x.Fill(myDataSet)

I execute the SELECT statement in TOAD/ SQL PLUS and that works fine. The
SELECT statement is a bit complex with lots of outer joins. If I replace it
with a more simple select statement, the procedure executes properly and the
datareader is populated

I then converted the procedure into a function returning the cursor and
executed it on SQL Plus to test if the REFCURSOR is returning empty handed,
such that

var results refcursor
exec :results := PackageName.getAllOrders(ContactId);
print results;

And again I see the output in SQL Plus. Why this is not reflecting in the
DataReader is driving me crazy. The complex SQL has 13 joins (very neccesary
given the nature of the legecy database I am working with). The problem is
also not in the way I am handling the .NET code because the .NET
function/stored procedure works with a simpler SELECT statement. I have
other stored procedures defined similairly that work properly . I have also
followed the MSDN examples to see of there is any difference. There is none.

Is there any limitations in returning complex selects in a REF CURSOR?
Any idea would be greatly appreciated.

Many thanks,
Wazir

 

[David Browne]

Dear All,

I am having a really strange problem with reading cursors from an Oracle 9i
database for a "particular" procedure. My DataReader does not contain any
rows after I execute the command object. I have tried

cmd.ExecuteReader

and

x = DataAdaptor(cmd)
x.Fill(myDataSet)

I execute the SELECT statement in TOAD/ SQL PLUS and that works fine. The
SELECT statement is a bit complex with lots of outer joins. If I replace it
with a more simple select statement, the procedure executes properly and the
datareader is populated

I then converted the procedure into a function returning the cursor and
executed it on SQL Plus to test if the REFCURSOR is returning empty handed,
such that

var results refcursor
exec :results := PackageName.getAllOrders(ContactId);
print results;

And again I see the output in SQL Plus. Why this is not reflecting in the
DataReader is driving me crazy. The complex SQL has 13 joins (very neccesary
given the nature of the legecy database I am working with). The problem is
also not in the way I am handling the .NET code because the .NET
function/stored procedure works with a simpler SELECT statement. I have
other stored procedures defined similairly that work properly . I have also
followed the MSDN examples to see of there is any difference. There is none.

Is there any limitations in returning complex selects in a REF CURSOR?
Any idea would be greatly appreciated.

What happens if you use a CommandText of

begin :results := PackageName.getAllOrdersContactId); end;

and bind an output OracleRefCursor and and input ContactId%TYPE parameter?

What version of ODP.NET and the Oracle Client are you using?
Update those and if the problem persists post a repro to the ODP.NET forum
at Oracle.

http://forums.oracle.com/forums/forum.jsp?start=0&forum=146

David

 

[MW]

I am using MS .NET Framework Data Provider for Oracle version 1.0.1012.0
(System.Data.OracleClient.dll) and Oracle9i Enterprise Edition Release
9.2.0.4.0.

Thank you for your response. Since my procedure was working for simpler
SELECT statements, I built its complexity slowly testing the resultset in
..NET each time. It seems to me that when I do a MAX from one of the lower
tables, the resultset returns empty-handed. I have modifed my query to
include a nested table and derive my MAX column from it and its working now.
Although I am not sure why it did not work before. It still puzzles me a
great deal.

I dont have an OracleRefCursor object available. Is this a part of the Data
Provider from Oracle? Is there any website that can give me a comparitive
chart between the two providers?

Thanks once again,
Wazir

 

[David Browne]

I am using MS .NET Framework Data Provider for Oracle version 1.0.1012.0
(System.Data.OracleClient.dll) and Oracle9i Enterprise Edition Release
9.2.0.4.0.

Thank you for your response. Since my procedure was working for simpler
SELECT statements, I built its complexity slowly testing the resultset in
.NET each time. It seems to me that when I do a MAX from one of the lower
tables, the resultset returns empty-handed. I have modifed my query to
include a nested table and derive my MAX column from it and its working now.
Although I am not sure why it did not work before. It still puzzles me a
great deal.

I dont have an OracleRefCursor object available. Is this a part of the Data
Provider from Oracle?
Yes.
Is there any website that can give me a comparitive
chart between the two providers?

Not that I know of, but they are very very similar. ODP.NET gives you
access to more cool Oracle features like Array Binding, PL/SQL tables, and
Oracle XML. Plus it supports multiple ref cursors, and has better support
than the MS driver.

Go to http://otn.oracle.com/tech/windows/odpnet/index.html and get ODP.NET.
The install includes the 9i version of the Oracle Client, which is likely
where your error is occuring.

David

 

[MW]

Many thanks David

 

[Marcel Sottnik]

Hallo

I noticed this strange behaviour too. But in my case it had nothing to do
with stored procedure, it was a direct select put as statement in the
command text.

I found out following things:

1. Empty result set comes if there is an aggregate function in the column
list of select which has to process NULL values and its result is NULL
value.
2. I tried following Oracle .NET providers: OraDirect from CoreLabs, Oracle
9.2.04, ComponentOne, original Microsoft Oracle .NET provider. All of them
except Microsoft returns in this case empty result set. The Microsoft's
Oracle .NET provider produces correct output.

I could not find out why does it work only with MS's Oracle data provider.