After updating from the native .NET oracle data provider to Oracles ODP.NET, I've run into the following issue.
I have a stored procedure that is part of a package, and it accepts 32 parameters, all IN but one, designated as an OUT parameter. Anyway, here is the issue I'm having. I have a date parameter passed in as a varchar2 and then 3 other parameters that are varchar2 as well. As for the rest, they are all NUMBER types.
In my .NET code all the values passed to the stored procedure are either string or int, with 4 exceptions. I have 4 items of data of type UInt32. The value they hold is too large for an int so uint was used.
Here is the problem. When we were using the native .NET oracle data provider, the parameter constructor includes a datatype of OracleType.Number, now the oracle parameter constructor has OracleDbType.Int32 and OracleDbType.Int64. The uint datatype didn't have a problem when set as the value of a parameter of datatype OracleType.Number, but now with the new client, I get different errors depending upon the datatypes I use.
(By the way, the stored procedure runs fine when called with the values I'm passing in. These exceptions all occur within the ExecuteNonQuery statement of an Oracle Command object instance.)
I've changed the datatype holding my values as well as the datatype of the parameter, using the following combinations. Here are the results of each.
.NET Datatype 'uint'
ODP .NET Parameter data type 'OracleDbType.Int32'
*System.OverflowException: Value was either too large or too small for an Int32.
at System.Convert.ToInt32(UInt32 value)
at System.UInt32.System.IConvertible.ToInt32(IFormatProvider provider)
at System.Convert.ToInt32(Object value)
at Oracle.DataAccess.Client.OracleParameter.PreBind_Int32()
at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at ScoutLoaderService.EventLoader.Load()*
.NET Datatype 'uint'
ODP .NET Parameter data type 'OracleDbType.Int64'
Oracle.DataAccess.Client.OracleException
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1 at
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at ScoutLoaderService.EventLoader.Load()*
.NET Datatype 'Int64'
ODP .NET Parameter data type 'OracleDbType.Int64'
Oracle.DataAccess.Client.OracleException
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at ScoutLoaderService.EventLoader.Load()*
Best Answer
The problem it turns out had nothing to do with the data types. I used the Int64 in the Oracle Parameter and it worked, but only after setting the BindByName property on my Command object to true.
Apparently the default for the .NET System.Data.OracleClient provider 'BINDING BY NAME', whereas the default for Oracle.DataAccess is 'BIND BY POSITION'.
It would have been nice if the Oracle Exception caught would have contained more information, like which parameter was throwing the exception. This may have helped me discover the issue sooner.