Oracle REF Cursors and OracleDataAdapter - data not populating

Hello All,

I have a simple stored procedure that returns a REF CURSOR. The query
retrieves 190 rows from the database (see query below). Following the
example laid out in the web page
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
(I'm using the last example - OracleDataAapter to fill a DataSet), the
procedure does not return any data unless I specify in the where
clause that the rownum be less than about 500 (don't know the exact
number).

I have verified that the stored procedure does in fact return the REF
CURSOR appropriately by calling the procedure and iterating through
the results in a Java class (this is without the rownum constraint).
Is there something I'm missing here - perhaps a setting on the
connection/command/parameter? Any help would be greatly appreciated.
Thanks in advance.

[CODE SNIPPETS]
-------
C# Code
-------
// Get connection
con = GetConnection();

// Create command
OracleCommand cmd = new OracleCommand("icrv_variance.load_deltas",
con);
cmd.CommandType = CommandType.StoredProcedure;

// Setup parameters
// Year
OracleParameter parm = new OracleParameter("p_year",
OracleType.Number);
parm.Value = year;
parm.Direction = ParameterDirection.Input;
cmd.Parameters.Add(parm);

// Cursor
parm = new OracleParameter("p_delta_cursor", OracleType.Cursor);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);

// Execute
logger.Debug("Executing stored procedure icrv_variance.load_deltas");
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "Deltas");
da.Fill(ds);
logger.Debug("Deltas retrieved: " + ds.GetXml());

// Close
logger.Debug("Closing connection");
con.Close();
con.Dispose();

-----------
PL/SQL Code
-----------
PROCEDURE load_deltas(p_year IN ICRV_VARIANCE_DELTA.YEAR%TYPE,
p_delta_cursor OUT CUR_DELTA)
IS
-- Variable declarations
-- Code location for logging error messages
codeLocation varchar2(255);

BEGIN
-- Create cursor
codeLocation := 'Opening cursor';

OPEN p_delta_cursor
FOR
select delta.year
,delta.org_code
,org.org_name
,delta.status
,sum(decode(delta.month, 1, delta.month, 0)) jan
,sum(decode(delta.month, 1, delta.delta_id, 0)) jan_id
,sum(decode(delta.month, 1, delta.delta_value, 0)) jan_value
,sum(decode(delta.month, 2, delta.month, 0)) feb
,sum(decode(delta.month, 2, delta.delta_id, 0)) feb_id
,sum(decode(delta.month, 2, delta.delta_value, 0)) feb_value
[... etc ...]
,sum(decode(delta.month, 12, delta.month, 0)) dec
,sum(decode(delta.month, 12, delta.delta_id, 0)) dec_id
,sum(decode(delta.month, 12, delta.delta_value, 0)) dec_value
from icrv_variance_delta delta
,organization_master org
where delta.org_code = org.org_code
and year = p_year
group by delta.year
,delta.org_code
,org.org_name
,delta.status
order by org.org_name;

EXCEPTION
-- Handle all exceptions
WHEN OTHERS THEN
-- Raise an application error
RAISE_APPLICATION_ERROR(-20001, 'Unexpected Error ' ||
codeLocation || ': ' || TO_CHAR(SQLCODE) || ', ' || SQLERRM);

END load_deltas;

Cowboy
Thu Mar 04 13:34:45 CST 2004

I am not sure. I have been using REF CURSORS with 9i for all of our apps for
the past few months. The only thing I can see that is different is I have
extended the data App Block for Oracle. Here is a sample (from a mock up, so
it is not properly formatted, et al):

private void BindCounties()
{
string connString=Helper.GetCOnfigString();
string sproc="DEM_GETCNTY_BYSTATE";

long StateID = 840047;

DataSet CountyDataSet = new DataSet();

OracleParameter oracleParam1 = new OracleParameter("STATE_2003_ID",
OracleType.Number);
OracleParameter oracleParam2 = new
OracleParameter("COUNTYCUR",OracleType.Cursor);
oracleParam1.Direction = ParameterDirection.Input;
oracleParam1.Value = StateID;
oracleParam2.Direction = ParameterDirection.Output;

string[] tableNames = {"CountyMenu"};

//Fill the datasets
OracleHelper.FillDataset(connString, sproc, CountyDataSet,
tableNames, oracleParam1, oracleParam2);

ddlCounty.DataSource = CountyDataSet.Tables["CountyMenu"];
ddlCounty.DataTextField = "COUNTY_NM";
ddlCounty.DataValueField = "DEM_COUNTY_2003_ID";
ddlCounty.DataBind();
}

Unmunge my email and send me a note if you want the OracleHelper class. The
user is
gbworld and the domain is comcast.net. I can fire off a copy for you.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
"Brian Barbash" <brian_barbash@hotmail.com> wrote in message
news:742105.0403040810.53d2e0d6@posting.google.com...
> Hello All,
>
> I have a simple stored procedure that returns a REF CURSOR. The query
> retrieves 190 rows from the database (see query below). Following the
> example laid out in the web page
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> (I'm using the last example - OracleDataAapter to fill a DataSet), the
> procedure does not return any data unless I specify in the where
> clause that the rownum be less than about 500 (don't know the exact
> number).
>
> I have verified that the stored procedure does in fact return the REF
> CURSOR appropriately by calling the procedure and iterating through
> the results in a Java class (this is without the rownum constraint).
> Is there something I'm missing here - perhaps a setting on the
> connection/command/parameter? Any help would be greatly appreciated.
> Thanks in advance.
>
> [CODE SNIPPETS]
> -------
> C# Code
> -------
> // Get connection
> con = GetConnection();
>
> // Create command
> OracleCommand cmd = new OracleCommand("icrv_variance.load_deltas",
> con);
> cmd.CommandType = CommandType.StoredProcedure;
>
> // Setup parameters
> // Year
> OracleParameter parm = new OracleParameter("p_year",
> OracleType.Number);
> parm.Value = year;
> parm.Direction = ParameterDirection.Input;
> cmd.Parameters.Add(parm);
>
> // Cursor
> parm = new OracleParameter("p_delta_cursor", OracleType.Cursor);
> parm.Direction = ParameterDirection.Output;
> cmd.Parameters.Add(parm);
>
> // Execute
> logger.Debug("Executing stored procedure icrv_variance.load_deltas");
> OracleDataAdapter da = new OracleDataAdapter(cmd);
> da.TableMappings.Add("Table", "Deltas");
> da.Fill(ds);
> logger.Debug("Deltas retrieved: " + ds.GetXml());
>
> // Close
> logger.Debug("Closing connection");
> con.Close();
> con.Dispose();
>
> -----------
> PL/SQL Code
> -----------
> PROCEDURE load_deltas(p_year IN ICRV_VARIANCE_DELTA.YEAR%TYPE,
> p_delta_cursor OUT CUR_DELTA)
> IS
> -- Variable declarations
> -- Code location for logging error messages
> codeLocation varchar2(255);
>
> BEGIN
> -- Create cursor
> codeLocation := 'Opening cursor';
>
> OPEN p_delta_cursor
> FOR
> select delta.year
> ,delta.org_code
> ,org.org_name
> ,delta.status
> ,sum(decode(delta.month, 1, delta.month, 0)) jan
> ,sum(decode(delta.month, 1, delta.delta_id, 0)) jan_id
> ,sum(decode(delta.month, 1, delta.delta_value, 0)) jan_value
> ,sum(decode(delta.month, 2, delta.month, 0)) feb
> ,sum(decode(delta.month, 2, delta.delta_id, 0)) feb_id
> ,sum(decode(delta.month, 2, delta.delta_value, 0)) feb_value
> [... etc ...]
> ,sum(decode(delta.month, 12, delta.month, 0)) dec
> ,sum(decode(delta.month, 12, delta.delta_id, 0)) dec_id
> ,sum(decode(delta.month, 12, delta.delta_value, 0)) dec_value
> from icrv_variance_delta delta
> ,organization_master org
> where delta.org_code = org.org_code
> and year = p_year
> group by delta.year
> ,delta.org_code
> ,org.org_name
> ,delta.status
> order by org.org_name;
>
> EXCEPTION
> -- Handle all exceptions
> WHEN OTHERS THEN
> -- Raise an application error
> RAISE_APPLICATION_ERROR(-20001, 'Unexpected Error ' ||
> codeLocation || ': ' || TO_CHAR(SQLCODE) || ', ' || SQLERRM);
>
> END load_deltas;