DAAB - ODAC - Stored Proc returning a cursor

Topics: Data Access Application Block
Jul 5, 2011 at 6:17 PM

I'm trying to get the Enterprise Library 5.0 working with the ODAC-provider for Oracle.

In the Configuration-editor, you can choose this from the list, but when I'm trying to run my code,I got an error saying:

Oracle.DataAccess.Client.OracleException was unhandled by user code
  Message=ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_GETONETABLEA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


When I take a look at the SP I see that a cursor is used:


procedure sp_getOneTableA (p_tableaid           IN  TABLEA.TABLEAID%TYPE,
                           p_cursor             OUT OUTPUT_REF_CURSOR,
                           errorcode            OUT NUMBER)IS
OPEN p_cursor FOR SELECT * FROM TABLEA WHERE tableaid = p_tableaid;

In my code I use a default rowmapper because my properties are the same as the columnnames



	public class TableA
		public int TableAId { get; set; }

		public int Label { get; set; }

		public DateTime DateCreated { get; set; }


In the database:



I've already created an IParameterMapper to send my id to the SP.Do I have to create another class to map the cursor to my object?



	public class ParamtereMapperGetOneTableA : IParameterMapper
		public void AssignParameters(DbCommand command, object[] parameterValues)
			DbParameter parameter = command.CreateParameter();
			parameter.ParameterName = "p_tableaid";
			parameter.Value = parameterValues[0];
The call itself:
		public TableA GetOneTableA(int tableAId)
			var db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
			IRowMapper<TableA> rowMapper = MapBuilder<TableA>.BuildAllProperties();
			IParameterMapper parameterMapper = new ParamtereMapperGetOneTableA();
			var accessor = db.CreateSprocAccessor<TableA>("Spck_tableAB.sp_getOneTableA", parameterMapper, rowMapper);
			var returnvalue = accessor.Execute(tableAId).FirstOrDefault();
			return returnvalue;

At home I use SQL-server and everything runs very smooth there and I would like to have the same advantageat work using Oracle and ODAC, 
but it seems to me that a lot of work needs to be done before even startingto think about using the DAAB with ODAC.

It's a lot of text here, but I wanted to show exactly where my problem is situated.
So to summarize my question(s):
Do I have to write a cutomized rowmapper when using a cursor?
Or should I use the IDataReader instead?
Is the use of EntLib with ODAC really that much harder then with MS SQL?

Thank you for your help.



Jul 6, 2011 at 4:16 AM


So you are using accessor here? Unfortunately, out paremeter is not supported when using accessor. Use other DAAB methods instead.


Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.

Jul 6, 2011 at 11:00 AM
Edited Jul 6, 2011 at 11:01 AM

Hello Noel,

I've changed my code by the use of a datareader, but I don't know how to add the cursor to the out parameters.
There seems to be no type that matches a cursor.


public class QueryHandler : IQueryHandler { public TableA GetOneTableA(int tableAId) { TableA returnValue = null; var db = EnterpriseLibraryContainer.Current.GetInstance<Database>(); using (DbCommand dbCommand = db.GetStoredProcCommand("Spck_tableAB.sp_getOneTableA")) { db.AddInParameter(dbCommand, "tableaid", DbType.Int32, tableAId); db.AddOutParameter(dbCommand,"errorcode",DbType.Int32,int.MaxValue); //Add a cursoras outparameter //db.AddOutParameter(dbCommand,"cursor",DbType. ); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { returnValue = new TableA(); returnValue.TableAId = Convert.ToInt32(dataReader["tableaid"]); returnValue.Label = dataReader["label"].ToString(); returnValue.DateCreated = Convert.ToDateTime(dataReader["datecreated"]); } } } return returnValue; }
Jul 6, 2011 at 11:14 AM

I really haven't worked DAAB with Oracle yet, but can you try specifying the type as OracleDbType.RefCursor?

 return new OracleParameter(name, OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);

I have just referred to this thread which I think have the same scenario like yours.


Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.

Jul 7, 2011 at 4:25 PM

Hello Noel,

your suggestion did work indeed.
Must have missed that one during my search...

Just another question, about "ExecuteNonQuery".
As I understand it correctly, it should return the number of affected rows after an update,
but when testing, it always returns -1. However, the update has been executed in the database.


		public void UpdateTableA(int tableAId, string label)
			var db = EnterpriseLibraryContainer.Current.GetInstance<Database>();

			using (DbCommand dbCommand = db.GetStoredProcCommand("Spck_tableAB.sp_upd_tableA"))
				db.AddInParameter(dbCommand, "p_tableaid", DbType.Int32, tableAId);
				db.AddInParameter(dbCommand, "p_label", DbType.String, label);
				db.AddOutParameter(dbCommand, "errorcode", DbType.Int32, int.MaxValue);

				var affectedRows = db.ExecuteNonQuery(dbCommand);

				if (affectedRows < 1)
					throw new DataException("Spck_tableAB.sp_upd_tableA");


Jul 8, 2011 at 4:57 AM

Have you checked if your Stored Procedure contains SET NOCOUNT ON?


Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.

Jul 8, 2011 at 7:27 AM


it wasn't in the SP.

BTW: for Oracle it will be SET FEEDBACK ON or OFF

Thank you very much for the help.