DAAB - ODAC - Stored Proc returning a cursor

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

Hello,
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

 

 

	[DataContract]
	public class TableA
	{
		[DataMember]
		public int TableAId { get; set; }

		[DataMember]
		public int Label { get; set; }

		[DataMember]
		public DateTime DateCreated { get; set; }
	}

 

In the database:

 

TABLEAID	NUMBER(4,0)
LABEL		VARCHAR2(50 BYTE)
DATECREATED	DATE	

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];
			command.Parameters.Add(parameter);
		}
	}
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 3:16 AM

Hi,

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.
entlib.support@avanade.com

Jul 6, 2011 at 10:00 AM
Edited Jul 6, 2011 at 10: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 10: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.
entlib.support@avanade.com

Jul 7, 2011 at 3: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.

Code:

		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 3:57 AM

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

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jul 8, 2011 at 6:27 AM

Noel,

it wasn't in the SP.

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

Thank you very much for the help.