REF CURSOR returns no rows

From: MW (mwazir77_ns_at_hotmail.com)
Date: 03/17/04 

Date: Wed, 17 Mar 2004 10:07:50 -0000

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

Relevant Pages

  • Re: REF CURSOR returns no rows
    ... > I execute the SELECT statement in TOAD/ SQL PLUS and that works fine. ... > Is there any limitations in returning complex selects in a REF CURSOR? ... What version of ODP.NET and the Oracle Client are you using? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Best Practices
    ... How you need to abstract database from ... maybe the same command object with a different command string)? ... Try not to design an application that uses one DataReader that is connected ... > define cursor 1 ...
    (microsoft.public.dotnet.framework.adonet)
  • Iteration of Large Result Set
    ... Large Table + DataReader Iteration = Slow ... Large Table + Cursor Iteration = Fast ... I have to stay away from a DataTable cause any single Account could have ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MS Best Practice - Load subset of a single Row
    ... I'd stay away from a cursor for this. ... You can create a databindable datareader - ... c and b suffer from the fact that you donot have query exec. ... > subset of the columns from a single row of a database table? ...
    (microsoft.public.dotnet.framework.adonet)
  • Deadlock in an application
    ... Findings: There are Selects, Inserts, Delete and Updates hapenning in the ... Each time the query is executed the sql engine would compile the ... The Insert/Updates were changed to execute using prepared ... The cursor or statements need to be closed right after use. ...
    (microsoft.public.sqlserver.jdbcdriver)
Loading