Oracle ODP.Net, ExecuteSqlNonQuery & Stored Procs

May 6, 2011 at 1:35 PM

I'm using the CTP with the Oracle.DataAccess.dll for ODP.NET so my provider is Oracle.DataAccess.Client.

My workflow is using an ExecuteSqlQuery<T> activity with an SQL statement without issue.

I then added an ExcuteSqlNonQuery activity to work with a stored proc & things aren't quite so smooth.

If I call an Oracle procedure with only In params, I'm fine.

If I add In/Out or Out params, I throw various flavors of Exceptions tied to the parameter type. Overflow errors for Number params, string buffer too small... for varchar2, etc.

I run into similar problems with the StoredProcedureReturnValue when calling Oracle Functions.

I understand that I've not provided any real details yet, but does anyone have some insight as to whether I may be adding my param information incorrectly in the designer or whether I shouldn't necessarily be expecting these to work under the ODP Oracle provider?

 

May 8, 2011 at 5:11 PM
Edited May 8, 2011 at 5:18 PM

Found the problems.

The Oracle function exceptions are caused by how the StoredProcedureReturnValue parameter in the BeginExecuteDbCommand class is handled.

It's tacked onto the end of the parameter list (presumably because SQL Server likes it there) & Oracle calls require that the return value be the first parameter.

As for the procedure out params, I was able to get my string/varchar2 Out parameters to properly update once I changed the dbParameter.Size = -1 statement in the same class to be a positive value.

I'll do some more experimentation with various return types, make some changes to the necessary library class(es) based on detecting an Oracle provider, then back to my Workflow solution.

This is not a particularly active topic, but thought I'd note this to save anyone else some time if they're using Oracle & the activity pack.