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
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:
End Using 'close connection conn.Close() End Using
3- If everything else failed, rewrite AddNewUploadedDocument using sql statements instead of stored procedure.