ODP.Net - Ref Cursor

[dabi]

Hi, I have an Oracle stored procedure that has a REF CURSOR as an output parameter. I am trying to invoke the procedure from Spring.Net by the given command:

DataTable dtResult = AdoTemplate.ClassicAdoTemplate.DataTableCreate(Com mandType.StoredProcedure, "<storedprocName>", parameterCollection);

However, I am unable to create the parameter list as there is no enumeration for RefCursor????

Please suggest.

Thanks
Dabi

[vsarathy]

We are having the same problem.
System.Data.DbType does not have an enumeration for REF CURSOR.
However, if we use System.Data.OracleClient.OracleType.Cursor will it work?
Even if it does, it breaks the DbProvider abstraction used by the data access objects. Is there a more elegant solution?
Viji

[Mark Pollack]

Hi,

The signatures on IDbParameters interface refer to a general Enum, not System.Data.DbType so that you can pass in a System.Data.OracleClient.OracleType.Cursor. The abstraction here is a very loose contract to accomodate these cases. Let me know if you got this to work, if not I'll make an issue ticket to fix it/investigate

Cheers,
Mark

[dabi]

Doesn't work. It gives the following exception:
Spring.Dao.TypeMismatch.DataAccessException:

{"Invalid parameter type specified. [System.Data.OracleClient.OracleType] is nt of expected type [Oracle.DataAccess.Client.OracleDbType]"}

StackTrace: " at Spring.Data.Common.DbParameters.AssignParameterTyp e(IDbDataParameter parameter, Enum parameterType)\r\n at Spring.Data.Common.DbParameters.AddParameter(Strin g name, Enum parameterType, Int32 size, ParameterDirection direction, Boolean isNullable, Byte precision, Byte scale, String sourceColumn, DataRowVersion sourceVersion, Object parameterValue)\r\n

I am using ODP.Net Data provider, so I thouht of using Oracle.DataAccess.Client.OracleDbType.RefCursor in the paramter definition. This does not work either. It gives the error "Object reference not set to an instance of an Object" for some reason??

Could you please take a look.
Thanks
-Dabi

[Mark Pollack]

Hi Dabi,

The error you show does look like you are passing in a value from the microsoft provider. You should indeed specify an instance of Oracle.DataAccess.Client.OracleDbType.RefCursor from ODP. Can you post the stack trace around "Object reference not set to an instance of an Object" in the meantime before I set up my own test case.

Thanks,
Mark

[dabi]

Hi Mark,
Here's the stack trace for the exception:

System.NullReferenceException: {"Object reference not set to an instance of an object."}

" at Spring.Data.Common.DbParameters.AssignParameterTyp e(IDbDataParameter parameter, Enum parameterType)\r\n at
Spring.Data.Common.DbParameters.AddParameter(Strin g name, Enum parameterType, Int32 size, ParameterDirection direction, Boolean isNullable, Byte precision, Byte scale, String sourceColumn, DataRowVersion sourceVersion, Object parameterValue)\r\n at Spring.Data.Common.DbParameters.AddOut(String name, Enum parameterType)\r\n at Dmp.RefMgr.Dao.AssociationsDao.GetHierarchies() in C:\\VSProjects\\DataManagementProject\\DMP-Spring\\RefMgr\\RefMgr\\Dao\\AssociationsDao.cs:li ne 26\r\n at Default3.Page_Load(Object sender, EventArgs e) in c:\\VSProjects\\WebSites\\MasterRef-Spring\\Default3.aspx.cs:line 22\r\n at System.Web.Util.CalliHelper.EventArgFunctionCaller (IntPtr fp, Object o, Object t, EventArgs e)\r\n at System.Web.Util.CalliEventHandlerDelegateProxy.Cal lback(Object sender, EventArgs e)\r\n at System.Web.UI.Control.OnLoad(EventArgs e)\r\n at System.Web.UI.Control.LoadRecursive()\r\n at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)"

I am using the following command to create and assign the parameter:

IDbParameters param = CreateDbParameters();
param.AddOut("P_REF_OUT", OracleDbType.RefCursor);

I also tried:
param.Add("P_REF_OUT", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

Thanks
Neha

[dabi]

Any updates on the above??

Thanks
Dabi

[Mark Pollack]

Hi Dabi,
Sorry, the previous post slipped me by... It looks like the line number got cut off, can you tell me the line number in the method AssignParameterType where there is the null exception. My guess is that it is line accessing the IDbDataParameter... In anycase, I'll try it out asap and get back to you. Apologies for the delay.

Regards,
Mark

[Mark Pollack]

Hi Dabi,

I found the bug, there was an incorrect listing for parameterDbTypeProperty in dbproviders.xml for the provider OracleODP-2.0.

It was listed as


<constructor-arg name="parameterDbTypeProperty" value="OracleDbType, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342"/>



but should be

<constructor-arg name="parameterDbTypeProperty" value="OracleDbType"/>


Since it is just the name of the property on OracleParameter that is of the type OracleDbType. I've added assert statement in DbProvider itself so that any resolution of a string to a PropertyInfo or MethodInfo will throw an error when the DbProvider is created. That should help to catch these mistakes earlier.

You can get the fix from the nightly downloads or make the change yourself in your local copy and rebuild. I logged this as JIRA issue SPRNET-692 (http://opensource.atlassian.com/projects/spring/browse/SPRNET-692).

Cheers,
Mark

[dabi]

Hi Mark,

Thanks! Changing the dbProviders.xml file worked. The nightly builds don't have the updated dbProviders.xml though.

Thanks
Dabi