Problem: Trying to call a packaged stored procedure, but the call is failing depending on the values of the parameters.
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
Procedure Definition:
procedure DUP_EXACT (
SSN in VARCHAR2,
LASTNAME in VARCHAR2,
FIRSTNAME in VARCHAR2,
MASTERRECORD IN VARCHAR2 DEFAULT NULL,
C_Table out sp_cursor)
Parameter Creation:
For Each SearchParameter In SearchParameters
ValueParameter = New OracleParameter
ValueParameter.Direction = ParameterDirection.Input
ValueParameter.OracleDbType = OracleDbType.Varchar2
ValueParameter.ParameterName = SearchParameter.ParameterFieldName
If Not SearchParameter.TransformedFieldValue = Nothing Then
ValueParameter.Value = SearchParameter.TransformedFieldValue
Else
ValueParameter.Value = String.Empty
End If
ExactMatchSearchParameters.Add(ValueParameter)
Next
Dim MasterRecordParameter As New OracleParameter()
MasterRecordParameter.Direction = ParameterDirection.Input
MasterRecordParameter.OracleDbType = OracleDbType.Varchar2
MasterRecordParameter.ParameterName = "MASTERRECORD"
MasterRecordParameter.Value = DBNull.Value
ExactMatchSearchParameters.Add(MasterRecordParameter)
Dim TableParameter As New OracleParameter
TableParameter.ParameterName = "C_Table"
TableParameter.OracleDbType = OracleDbType.RefCursor
TableParameter.Direction = ParameterDirection.Output
ExactMatchSearchParameters.Add(TableParameter)
Execution:
Using Command As OracleCommand =
New OracleCommand(
QualifiedProcedureName,
Me.Database.Connection)
Command.CommandType = CommandType.StoredProcedure
'Command.AddToStatementCache = False '
For Each Parameter In Parameters
Command.Parameters.Add(Parameter)
Next
Command.Connection.Open()
'Command.Connection.FlushCache() '
Using Reader As OracleDataReader = Command.ExecuteReader()
Example Successes & Failures:
*** SUCCESS ***
[SSN]: "6#######0"
[LASTNAME]: "W_____x"
[FIRSTNAME]: "D______e"
[MASTERRECORD]: ""
[C_Table]: ""
*** FAILURE ***
[SSN]: "2#######_1"
[LASTNAME]: "C____n"
[FIRSTNAME]: "L___e"
[MASTERRECORD]: ""
[C_Table]: ""
*** FAILURE ***
[SSN]: "5#######5"
[LASTNAME]: "C_______s"
[FIRSTNAME]: "R_____o"
[MASTERRECORD]: ""
[C_Table]: ""
*** SUCCESS ***
[SSN]: "6#######0"
[LASTNAME]: "P___a"
[FIRSTNAME]: "N______r"
[MASTERRECORD]: ""
[C_Table]: ""
Additional Testing:
I tried running a trace to see what ODP.NET was actually sending to the database in the parameters, but the tracefiles did not provide any meaningful information (IE: the actual parameter values)
TIME:2013/02/14-14:10:19:678
TID:231c
OpsSqlPrepare2():
SQL: Begin PACKAGE.DUP_EXACT(:v0, :v1, :v2, :v3, :v4); End;
Example Parameter Values:
?Command.Parameters(0)
{SSN}
ArrayBindSize: Nothing
ArrayBindStatus: Nothing
CollectionType: None {0}
DbType: String {16}
Direction: Input {1}
InvalidPrecision: 100
InvalidScale: 129
InvalidSize: -1
IsNullable: False
m_bOracleDbTypeExSet: False
m_bReturnDateTimeOffset: False
m_collRef: {Oracle.DataAccess.Client.OracleParameterCollection}
m_commandText: ""
m_direction: Input {1}
m_disposed: False
m_enumType: ORADBTYPE {4}
m_modified: False
m_oraDbType: Varchar2 {126}
m_paramName: "SSN"
m_paramPosOrName: ""
m_saveValue: Nothing
MaxScale: 127
MinScale: -84
Offset: 0
OracleDbType: Varchar2 {126}
OracleDbTypeEx: Varchar2 {126}
ParameterEnumType: ORADBTYPE {4}
ParameterName: "SSN"
Precision: 0
Scale: 0
Size: 0
SourceColumn: ""
SourceColumnNullMapping: False
SourceVersion: Current {512}
Status: Success {0}
UdtTypeName: ""
Value: "4#######0" {String}
The answer is that there is a bug in Oracle 9.2.0.6.0 that causes intermitten VARCHAR2 binding errors. Awesome.
This forum post finally gave me the answer:
Is this behavior consistent or intermittent? If intermitten, and your db is 9206, you're likely encountering the following rdbms bug:
Bug.4015165 (74) REGRN SCALAR VARCHAR2 IN BINDS WITH DIFFERENT SIZE RANDOMLY FAILS WITH ORA-06502
and should be resolved by patching your database.
I'm not sure why, but ODP seems to encounter this bug a lot more than other drivers.
If not intermittent, or if you are using a current patch level of database, a complete testcase would probably be best.
Cheers, Greg
A quick check verified that the version we are on is affected:
select * from v$version;
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
Luckily our production servers are 10g, so we finally updated our dev server to 10g as well, and viola, no more problems.
Answer Trail: