locked
Cannot pass ref cursor to DataSet in ODP.Net RRS feed

  • Question

  • Hi I'm using Microsoft Library Enterprise 5.0 with ODP.Net for oracle 10g.

    I cannot read cursor to DataSet, it works fine before, last time i'm using System.Data.OracleClient, it works fine, but after change to Oracle.DataAccess.Client, it doesn't work, please help me.

    my SP:

    PROCEDURE Sp_tsmcompany_select(
            p_companykey IN tsmcompany.companykey%Type DEFAULT NULL,
            p_companyid IN tsmcompany.companyid%Type DEFAULT NULL,
            p_companyname IN tsmcompany.companyname%Type DEFAULT NULL,
            p_primaryAddrkey IN tsmcompany.primaryAddrkey%Type DEFAULT NULL,
            p_cntctkey IN tsmcompany.cntctkey%Type DEFAULT NULL,
            p_cr IN OUT sys_refcursor) AS
         v_query varchar2(400);
        BEGIN
            v_query := ' WHERE COMPANYKEY > 0 ';
           
            IF p_companykey IS NOT NULL THEN
                v_query := v_query||' AND companykey='||p_companykey;
            END IF;
           
            IF p_companyid IS NOT NULL THEN
                v_query := v_query||' AND companyid='||p_companyid;
            END IF;
           
            IF p_companyname IS NOT NULL THEN
                v_query := v_query||' AND companyname='||p_companyname;
            END IF;
           
            IF p_primaryAddrkey IS NOT NULL THEN
                v_query := v_query||' AND primaryAddrkey='||p_primaryAddrkey;
            END IF;
           
            IF p_cntctkey IS NOT NULL THEN
                v_query := v_query||' AND cntctkey='||p_cntctkey;
            END IF;
            v_query := 'SELECT * FROM tsmcompany '||v_query;
           
            OPEN p_cr FOR v_query;
           
        END;

     

    C# code:

    public DataSet getCompanyList()
            {
                try
                {
                    string sqlCommand = String.Format("pkg_SM.Sp_tsmcompany_select");
                    DbCommand dbCommand = DB.GetStoredProcCommand(sqlCommand);				
                    OracleParameter param = new OracleParameter("p_cr"OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
                    dbCommand.Parameters.Add(param);
    				DataSet ds = DB.ExecuteDataSet(dbCommand);
                    ds.Tables[0].TableName = "Company";
                    return ds;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    When i call this function, it shows me error message like this:

     

    An unexpected exception occurred while calling HandleException with policy 'Unhandled Policy'.
    System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidCastException: The OracleParameterCollection only accepts non-null OracleParameter type objects, not OracleParameter objects

    Please help me.

    Thanks and best regards

    Thien Nguyen

    Tuesday, April 19, 2011 3:10 AM