Oracle REF Cursors and OracleDataAdapter - data not populating

Discussion in 'Microsoft ADO .NET' started by Brian Barbash, Mar 4, 2004.

  1. 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;
     
    Brian Barbash, Mar 4, 2004
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:...
    > 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;
     
    Cowboy \(Gregory A. Beamer\), Mar 4, 2004
    #2
    1. Advertisements

  3. Brian Barbash

    bhawna

    Joined:
    Oct 21, 2011
    Messages:
    1
    Likes Received:
    0
    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
     
    bhawna, Oct 21, 2011
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Recordsets and Ref Cursors

    Guest, Dec 28, 2004, in forum: Microsoft ADO .NET
    Replies:
    8
    Views:
    845
    Guest
    Feb 2, 2005
  2. news.microsoft.com
    Replies:
    9
    Views:
    1,848
    Joe Hodsdon
    Oct 13, 2005
  3. Tom Wells
    Replies:
    2
    Views:
    625
    Paul Clement
    Jan 12, 2006
  4. Dmitry

    DAAB with Oracle ref cursors

    Dmitry, Mar 16, 2006, in forum: Microsoft ADO .NET
    Replies:
    2
    Views:
    933
    Dmitry
    Mar 17, 2006
  5. Knickerless Parsons

    Easy Question - Oracle Ref Cursors

    Knickerless Parsons, Sep 28, 2007, in forum: Microsoft ADO .NET
    Replies:
    1
    Views:
    268
    Guest
    Oct 9, 2007
Loading...

Share This Page