Oracle REF Cursors and OracleDataAdapter - data not populating

  • Thread starter Thread starter Brian Barbash
  • Start date Start date
B

Brian Barbash

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/d...-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;
 
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 said:
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/d...-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;
Click to expand...
 
Could you please post the OracleHelper's content as we are struggling a lot with oracle. I also want to fill the Datatable using Adapter but unable to do. As my sp is having one of its params as REF CURSOR.

-Thanks in advance,
Bhawna
 
Back
Top