system.data.oracleclient error

[Guest]

Greetings

I am contacting you because I can't find a resolution to this on your support website but have seen references to issues that may be related

I am attempting to use the .Net Managed Provider for Oracle. Whenver I execute a stored procedure that returns null values in parameters declared as ParameterDirection.Output and OracleType.Number I receive the following error

"System.Data.OracleClient.OracleException: OCI-22060: argument [2] is an invalid or uninitialized numbe

at System.Data.OracleClient.OracleException.Check(OciHandle errorHandle, Int32 rc
at System.Data.OracleClient.OracleNumber.InternalNeg(OciHandle errorHandle, Byte[] x, Byte[] result
at System.Data.OracleClient.OracleNumber.ToDecimal(OciHandle errorHandle, Byte[] value
at System.Data.OracleClient.OracleNumber.op_Explicit(OracleNumber x
at System.Data.OracleClient.OracleNumber.get_Value(
at System.Data.OracleClient.OracleParameterBinding.GetOutputValue(NativeBuffer parameterBuffer, OracleConnection connection, Boolean needCLSType
at System.Data.OracleClient.OracleParameterBinding.PostExecute(NativeBuffer parameterBuffer, OracleConnection connection
at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList& refCursorParameterOrdinals
at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciHandle& rowidDescriptor
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery(
at CityMH.Data.Oracle.Client.OraclClientHelper.ExecuteNonQuery(OracleConnection connection, CommandType commandType, String commandText, OracleParameter[] commandParameters) in C:\Local QUMA Workfiles\CityMH .Net Framework V2.0\CITYMH\Data\Oracle\Client\OracleClientHelper.vb:line 431

I tried the workaround conveyed in KB article 329163 (changed the parameter directions to InputOutput and set an initial value of 0)

While this did stop the above error from occuring, it also prevented any error from coming back from the stored procedure. I need to be able to catch the ORA-01403 that should be coming back from the stored procedure and convey the "data not found" error to the user. Therefore, this workaround cannot be used

I am using the following products

Microsoft.NET Framework 1.1 Version 1.1.432
MDAC 2.
Microsoft Visual Basic .NET 69461-270-0000007-1888
Oracle Client 8.1.7 Release

If there is a fix available, please advise
If you require any additional information, please let me know

Thanks

 

[Tian Min Huang]

Hello,

Thanks for your post. I found another thread of the same problem which was
resolved by changing the stored procedure with the VARCHAR return type
instead of NUMBER. Please check it:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=bpitma%2
4fov%241%40svr7.m-online.net&rnum=1&prev=/groups%3Fq%3D%2522OCI-22060%2522%2
6hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26sa%3DN%26tab%3Dwg

Hope this helps.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Tian Min Huang]

Hi,

Thanks for your reponse. I think more information is needed before moving
forward:

Could you post a simple project and stored procedure which are able to
reproduce the problem? I will be glad to check it on my side.

In the meantime, I suggest that you can also use Oracle Data Provider for
.NET instead of Microsoft OLE DB Provider for Oracle to check if it
resolves the problem. Please refer to the following pages for detailed
information:

Oracle Data Provider for .NET
http://otn.oracle.com/software/tech/windows/odpnet/index.html

I look forward to your response.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Guest]

Hi Tim

What we were really hoping was that there was a patch available to correct this without using a workaround. We have determined a workaround that we can use but still look forward to the eventual repair of this useful but flawed provider
I have included the info you requested.

For anyone interested, the workaround is to add a parameter to your Strored Procedure argument list and add an exception block to catch the ORA-01403. Other ORA errors such as integrity violation seem to be raised properly so are left to be handled by the ap

/* add this param to your SP argument list *
ReturnValue OUT VARCHAR

/*initialize to an OK value *
returnvalue:='0'

begi
select..

exceptio
when no_data_found the
returnvalue:= '1403'
end

back in your app check if there is a parameter named ReturnValue and if so, check it's value to determine if it is set to your OK value or if you have a no data found situation.

Here's some info about how we are setting up. The error is so easy to produce that you may as well use any table you already have or just create a very simple dummy table

CREATE OR REPLACE PROCEDURE READSOMETHIN
(pNumberParameter OUT NUMBER
A
BEGI
SELECT ANumberColum
INTO pNumberParameter
FROM WhateverTabl
WHERE SomeColumn=SomeValueYouWontFind
END

/* this procedure should produce an ORA-01403 to let you know your read was unsuccessful but if using the system.data.OracleClient provider with a very basic initial parameter setu
Dim _parameter As New OracleClient.OracleParamete
_parameter.ParameterName = "pNumberParameter
_parameter.OracleType = OracleClient.OracleType.Numbe
_parameter.Direction = ParameterDirection.Outpu
_parameter.Size =
_parameter.value = DBNull.Valu
you'll get an OCI-22060. If you use workaround techniques to manipulate the param you'll will not get the OCI-22060 but you also lose the ORA-01403 which hampers you in detecting that your read was not successful *

Thanks for your help!