Accessing procedure parameters with Oracle Odp database block

Monday, April 21st, 2008

The particularly astute among you will have noticed that we have been accessing parameters directly in the code I posted. Really we should be using the Set and Get methods that the database object provides. So instead of:

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCmd = db.GetStoredProcCommand("AUTH_LOGIN");
db.DiscoverParameters(dbCmd);
DbParameterCollection cmdParams = dbCmd.Parameters;
cmdParams["P_OSNAME"].Value = criteria.Username;
cmdParams["P_INCOMING_PASSWORD"].Value = criteria.Password;
db.ExecuteNonQuery(dbCmd);
authenticationReturnCode =
Convert.ToInt32(db.GetParameterValue(dbCmd, "P_RET_CODE"));

do

db.SetParameterValue(dbCmd, "P_OSNAME", criteria.Username);

and

authenticationReturnCode =
db.GetParameterValue(dbCmd, "P_OSNAME");

I strongly advise you do do this - a lot of my pain over the last few days would have been eased if I had.

Posted in Development | No Comments »

EntLib Data Access block with Oracle Odp OUT parameters

Friday, April 18th, 2008

My code has been failing when getting values out of procedures when I switch from the Microsoft provider to the Odp provider inside the Data Access App Block.
Here is some typical code:

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCmd = db.GetStoredProcCommand("AUTH_LOGIN");
db.DiscoverParameters(dbCmd);
DbParameterCollection cmdParams = dbCmd.Parameters;
cmdParams["P_OSNAME"].Value = criteria.Username;
cmdParams["P_INCOMING_PASSWORD"].Value = criteria.Password;
db.ExecuteNonQuery(dbCmd);
authenticationReturnCode =
Convert.ToInt32(db.GetParameterValue(dbCmd, "P_RET_CODE"));

This doesn’t work - it fails with an exception:
Unable to cast object of type ‘Oracle.DataAccess.Types.OracleDecimal’ to type ‘System.IConvertible’.

P_RET_CODE is defined as a NUMBER OUT in the procedure.

It seems that the DiscoverParameters call doesn’t set up the OUT parameter quite right, even though it looks fine. However, if you explicitly tell it the expected data type it decides to work (this line comes just before the ExecuteNonQuery):

cmdParams["P_RET_CODE"].DbType = System.Data.DbType.Decimal;

Posted in Development | No Comments »

Adapting the DAAB to use Oracle ODP.Net provider

Tuesday, April 15th, 2008

I’ve been doing a bit of tweaking of the Data Access Application Block to try and get around the problem described earlier.

I’ve replicated OracleDatabase, OracleDataReaderWrapper and the OracleDatabaseAssembler to reference the Odp provider (Oracle.DataAccess) rather than System.Data.OracleClient. I’ve called these classes OracleOdpDatabase etc. Inside them are some simple changes to enumeration values and data types (details on request).

Then a simple addition to the configuration details makes the block use the new objects rather than the old ones:
You need to add a providerMappings section inside the <dataConfiguration defaultDatabase="Dev" > section to indicate what new database class to use

<providermappings>
<add
databaseType= "Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleOdpDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"
name="Oracle.DataAccess.Client" >
<providermappings>

Note that I haven’t ramped the version number here which I suspect I should if I end up using this in production.

Then in the connectionStrings section just reference the Odp provider:

<add
name="Dev"
connectionString="Data Source=dev;User ID=owner;Password=owner;"
providerName="Oracle.DataAccess.Client" />

The ODP provider doesn’t recognise the Unicode item in the connection string that I have been using with the Microsoft provider.

This mostly works but I am having problems getting return values from procedures with OUT parameters. More details to follow…

Posted in Development | No Comments »

Oracle CLOB’s and the MS Data Access Application Block

Wednesday, April 9th, 2008

We have hit upon a problem with the Microsoft Data Access Application Block when trying to update / insert to an Oracle CLOB column; in short it doesn’t work.
In long: it seems to work until the total size of the data you are passing to the procedure is over ~32k. This actually seems to be a limitation of the System.Data.Oracle provider rather than anything the DAAB is doing.

Posted in Development | No Comments »