Oracle ODP.NET problems calling package procedure with parameters

Question

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}

Have you tried a test where you manually build your OracleParameter objects with hard coded values, and add them to your OracleCommand? ... to rule an issue with your dynamic code. — Walter Stabosz
– Walter Stabosz, Commented Feb 14, 2013 at 20:47

Community · Accepted Answer · 2017-05-23 12:04:50Z

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:

Parameter issue with Oracle RefCursor

Oracle ODP.NET Forum

ODP.NET Forum Thread "Error ORA-06502 PL/SQL"

Pierluc SS · Accepted Answer · 2013-02-15 14:47:49Z

1

It might sound odd, but make sure the oracle params are added as they appear in the query. I've ran into this issue a few times in the past (often in the migration to ODP.NET)

answered Feb 15, 2013 at 14:47

Pierluc SS

3,1767 gold badges33 silver badges44 bronze badges

2 Comments

Tom Halladay Over a year ago

Good reminder, but I'm pretty sure they are in order in the query, as the debug code that printed the success / failure messages was printing them out from a for loop of the parameters

Christian Shay Over a year ago

The default is bind by position, but you can set the bindbyname property to true to assist in migrating code from other providers such as the OracleClient.

Collectives™ on Stack Overflow

Oracle ODP.NET problems calling package procedure with parameters

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related