CODEWITHSUNDEEP
.netenterprise-libraryodp.netsys-refcursor
Martin
Martin

Reputation: 597

EntLibContrib and OdpNet

I'm trying to get a ref cursor returned from a oracle procedure back to my .Net app, but I keep getting an error regarding the wrong number of parameters.

The oracle procedure has the following params:

PROCEDURE sp_user_list(pv_username     IN VARCHAR2
                        ,pn_result      OUT NUMBER
                        ,pv_error       OUT VARCHAR2
                        ,pref_user_list OUT SYS_REFCURSOR);

My data access code is :

public int GetUserID(string sUserName)
        {
            int iUserID = 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = "pk_quality.sp_user_list";
            DbCommand cmd = db.GetStoredProcCommand(sql);

            db.AddInParameter(cmd, "pv_username", DbType.String, sUserName);
            db.AddOutParameter(cmd, "pn_result", DbType.Int32, 2);
            db.AddOutParameter(cmd, "pv_error", DbType.String, 500);


            using (IDataReader dataReader = db.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    iUserID = int.Parse(dataReader["user_id"].ToString());
                }
            }

            return iUserID;
        }

There doesn't seem to be any ref cursor dbtype to allow me to add an out parameter to handle the ref cursor. Am I doing something wrong?

Upvotes: 0

Views: 798

Answers (1)

Ambal
Ambal

Reputation: 215

You have to add the refcursor output parameter as well to the command object.

cmd.Parameters.Add(
     new OracleParameter("pref_user_list", 
                         OracleDbType.RefCursor, 
                         ParameterDirection.Output));

This works with ODP.NET data provider. The DbType enum doesn't have a corresponding type for refcursor. But, adding Oracle reference to data access class may look like violating the DAAB principle.

Upvotes: 1

Related Questions