Erroneous Oracle numeric or value error

Question

I have an ongoing issue in which a windows service gets this Oracle error:

Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

However, I am sure that I am passing a numeric value to Oracle. Working with our Oracle DBA and an Oracle support ticket we were able to capture what the Oracle server receives. My problem is with bind variable #10. The value Oracle reports in the trace log on the server is "A@Y8". However, the value I am actually sending is 167569173.

The Oracle value “A@Y8” does not look familiar – it’s not the value from another field. It is always the same value in the Oracle trace file – always A@Y8. But the numeric value that I am sending is different for each record.

The service pulls data every few minutes from an external source and inserts it into Oracle. It works for about two days and then I get the ORA-06502. Once I get the Oracle error I keep getting it on every pass until I restart the service. When the service restarts it works property for a few days – even though it is pulling the same data that was causing the error.

• The Windows Service is written in VB.Net.
• When I read the data I put the field into nullable Integer – Int32?.
• I send this variable to an Oracle package which throws the ORA-06502 and the trace value show the value of "A@Y8".
• I then log the value of the variable and my log shows it as numeric
  (it logs as 167569173).

The conversion error happens on the call to the procedure. That is, when trying to pass the parameter to the procedure no on the actual insert within the procedure.

Somehow, the Integer value in my code is not an Integer when Oracle receives it. There appears to be some corruption but I don’t think it is in Vb.Net. My field is an integer and would not hold the value Oracle reports. If the field did hold that value then it should log that value when I log it.


How can I see what value the Oracle client is sending to the Oracle server? I cannot find any relevant client logs.

Any ideas on what could be causing this?

Here is the Oracle trace entry:

Bind#10
oacdty=01 mxl=32(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=178 siz=0 off=480
kxsbbbfp=9fffffffbed31848 bln=32 avl=06 flg=01
value="A@Y8" 
EXEC #11529215044982021440:c=0,e=1300,p=0,cr=34359738368,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=2656312500288
ERROR #4:err=6502 tim=2656312500311

Here is my code: This is the method that inserts into the database - it throws the error:

Public Shared Sub AddNewUploadedDocument(document As ManualUploadDocument)
    Using conn As OracleConnection = DataFactory.GetConnection()
        Using command As New OracleCommand("OM.EXTERNAL_DOCUMENT_OBJECTS.insertUploadedDocuments", conn)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add("tSelecteeID", document.SelecteeID)
            command.Parameters.Add("tFileName", document.FileNameGUID)
            command.Parameters.Add("tFileType", document.FileType)
            command.Parameters.Add("tOriginalFileName", document.OriginalFileName)
            command.Parameters.Add("tOriginalFileType", document.OriginalFileType)
            command.Parameters.Add("tDocTypeID", DBNull.Value)
            command.Parameters.Add("tStatus", document.Status.ToString())
            command.Parameters.Add("tSource", Integer.Parse(document.Source))
            command.Parameters.Add("tSubmitted", Convert.ToInt32(document.Submitted))
            command.Parameters.Add("tIsStaffingConversion", Convert.ToInt32(document.IsStaffingConversion))
            command.Parameters.Add("tStaffingDocumentationID", document.StaffingDocumentationID)

            command.ExecuteNonQuery()
        End Using
        'close connection
        conn.Close()
    End Using

End Sub

After this, I call this method to log the values:

Private Shared Function getInsertAttemptFields(document As OnBoardingDataObjects.ManualUploadDocument) As String
    Dim sb As New StringBuilder()
    sb.AppendLine("Insert Fields")
    sb.Append("tSelecteeID:")
    sb.AppendLine(document.SelecteeID)
    sb.Append("tFileName:")
    sb.AppendLine(document.FileNameGUID)
    sb.Append("tFileType:")
    sb.AppendLine(document.FileType)
    sb.Append("tOriginalFileName:")
    sb.AppendLine(document.OriginalFileName)
    sb.Append("tOriginalFileType:")
    sb.AppendLine(document.OriginalFileType)
    sb.Append("tDocTypeID:")
    sb.AppendLine("<null>")
    sb.Append("tStatus:")
    sb.AppendLine(document.Status.ToString())
    sb.Append("tSource:")
    sb.AppendLine(Integer.Parse(document.Source))
    sb.Append("tSubmitted:")
    sb.AppendLine(Convert.ToInt32(document.Submitted))
    sb.Append("tIsStaffingConversion:")
    sb.AppendLine(Convert.ToInt32(document.IsStaffingConversion))
    sb.Append("tStaffingDocumentationID:")
    sb.AppendLine(document.StaffingDocumentationID)

    Return sb.ToString()
End Function

It is the same object that I pass to both methods. The StaffingDocumentationID field is the one that the Oracle trace shows as A@Y8 and the log shows as 167569173.

Here is the definition of the manualUploadDocument object that I am passing around. For brevity, I remove the code in some methods - the properties are what is relevant here and the removed code does not reference them.

<Serializable()>
Public Class ManualUploadDocument

    Private _Submitted As Boolean = False

    Public Property DocumentUploadID As Integer
    Public Property SelecteeID As Integer
    Public ReadOnly Property FileName As String
        Get
            Return FileNameGUID + "." + FileType
        End Get
    End Property
    Public Property FileType As String
    Public Property OriginalFileName As String = ""
    Public Property OriginalFileType As String = ""
    Public Property DocumentTypeID As Integer
    Public Property DocumentType As String = ""
    Public Property UploadDate As Date
    Public Property FileNameGUID As String
    Public Property Status As PublicEnums.ManualUploadStatus
    Public Property Source As PublicEnums.ManualUploadSource
    Public Property IsMarkedFortransmission As Boolean = False
    Public Property TransmissionStatusDescription As String
    Public Property IsStaffingConversion As Boolean = False
    Public Property StaffingDocumentationID As Int32?


    Public Property Submitted As Boolean
        Get
            Return _Submitted
        End Get
        Set(value As Boolean)
            _Submitted = value
        End Set
    End Property


    Public Shared Function getStatusDisplayName(status As PublicEnums.ManualUploadStatus) As String
        Dim name As String = ""
        'Some code here

        Return name
    End Function

    Public Shared Function getStatusDescription(status As PublicEnums.ManualUploadStatus) As String
        Dim val As String = ""
        'Some code here

        Return val
    End Function

End Class

Answer 1

Well, I don't see any error in the provided codes. So, there should be some errors in OM.EXTERNAL_DOCUMENT_OBJECTS.insertUploadedDocuments stored procedure. In any case, I have three recommendations:

1- Make sure the stored procedure OM.EXTERNAL_DOCUMENT_OBJECTS.insertUploadedDocuments is correct.
2- Use a try catch block in AddNewUploadedDocument, like:

Public Shared Sub AddNewUploadedDocument(document As ManualUploadDocument)
    Using conn As OracleConnection = DataFactory.GetConnection()
        Using command As New OracleCommand("OM.EXTERNAL_DOCUMENT_OBJECTS.insertUploadedDocuments", conn)
            Try


            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add("tSelecteeID", document.SelecteeID)
            command.Parameters.Add("tFileName", document.FileNameGUID)
            command.Parameters.Add("tFileType", document.FileType)
            command.Parameters.Add("tOriginalFileName", document.OriginalFileName)
            command.Parameters.Add("tOriginalFileType", document.OriginalFileType)
            command.Parameters.Add("tDocTypeID", DBNull.Value)
            command.Parameters.Add("tStatus", document.Status.ToString())
            command.Parameters.Add("tSource", Integer.Parse(document.Source))
            command.Parameters.Add("tSubmitted", Convert.ToInt32(document.Submitted))
            command.Parameters.Add("tIsStaffingConversion", Convert.ToInt32(document.IsStaffingConversion))
            command.Parameters.Add("tStaffingDocumentationID", document.StaffingDocumentationID)

            command.ExecuteNonQuery()

            Catch Ex as Exception
                 Log (document.StaffingDocumentationID)
                 Log (Ex.StackTrace)
            End Try

End Using 'close connection conn.Close() End Using

End Sub

3- If everything else failed, rewrite AddNewUploadedDocument using sql statements instead of stored procedure.