PC Review


Reply
Thread Tools Rating: Thread Rating: 4 votes, 3.00 average.

Oracle REF Cursors and OracleDataAdapter - data not populating

 
 
Brian Barbash
Guest
Posts: n/a
 
      4th Mar 2004
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/de...datareader.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;
 
Reply With Quote
 
 
 
 
Cowboy \(Gregory A. Beamer\)
Guest
Posts: n/a
 
      4th Mar 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/de...datareader.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;





 
Reply With Quote
 
 
 
 
New Member
Join Date: Oct 2011
Posts: 1
 
      21st Oct 2011
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Easy Question - Oracle Ref Cursors Knickerless Parsons Microsoft ADO .NET 1 9th Oct 2007 12:35 PM
DAAB with Oracle ref cursors Dmitry Microsoft ADO .NET 2 17th Mar 2006 09:22 PM
OracleDataAdapter.Fill not mapping Oracle data types to .NET data types news.microsoft.com Microsoft ADO .NET 9 13th Oct 2005 08:01 PM
What's the difference between Cursor = Cursors.WaitCursor and CurrentCursor=Cursors.WaitCursor Just Me Microsoft VB .NET 10 24th Mar 2005 03:57 PM
Problem filling Strongly Typed DataSet from an Oracle 9i stored procedure that returns multiple ref cursors DotNetGruven Microsoft ADO .NET 2 21st Jul 2004 04:12 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:27 AM.