Cannot pass ref cursor to DataSet in ODP.Net

See more:

C#

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:


System.InvalidCastException: The OracleParameterCollection only accepts non-null OracleParameter type objects, not OracleParameter objects
Please help me.

Thanks and best regards

Thien Nguyen