([Oracle] [ODBC] numeric value out of range.) Oracle adapter in the BizTalk project. If the target field has a date type, an error is returned.

An error occurred while developing the project: "[Oracle] [ODBC] numeric value out of range ."

After checking, it should be that the data value exceeds the definition length of the field. However, after checking for a long time, no extra-long data is found.

 

Finally, I found a bug in Oracle adapter.

If the target is an Oracle database and Oracle adapter is used for data exchange, if the target table has a date field, which is not assigned a value, the problem may occur.
Error: "[Oracle] [ODBC] numeric value out of range ."

The test is as follows:

1. Create a table in the Oracle database:

ID is of the varchar2 (10) type, and data1 is of the date type.

2. Create a process project:

Source architecture:

Target architecture (that is, the newly created table architecture ):

Conversion Relationship. data1 is not assigned a value:

 

The process logic is relatively simple:

3. Test results:

The port is frozen and the process is suspended:

 

Error:

 

Code

The adapter cannot transmit messages to be sent to the sending port sendportcoredb (URL is oracledb: // coredb_9fb1d07f-a403-4f7d-954b-52218fc56fc2. After the Retry Interval specified for the sending port, the message will be re-transmitted. Details: "error Transmitting message: < SOAP-ENV: Fault Xmlns: SOAP-ENV = "Http://schemas.xmlsoap.org/soap/envelope" > < Faultcode > SOAP-ENV: Server </ Faultcode > < Faultstring > Request ID: Unknown
Exception type: system defined exception
Exception info:
Exception occurred:
Source: oracledb
Cause: oracledb: // exception = dbexception (unique ID & Lt; None & Gt; )
22003: [Oracle] [ODBC] numeric value out of range.

Exception data:
Struct dbexception =
Widestring statuscode = "22003"
Widestring description = "[Oracle] [ODBC] numeric value out of range ." </ Faultstring > < Detail > < Exposed: dbexception Xmlns: exposed = "Http://schemas.microsoft.com" > < Exposed: statuscode > 22003 </ Exposed: statuscode > < Exposed: Description > [Oracle] [ODBC] numeric value out of range. </ Exposed: Description > </ Exposed: dbexception > </ Detail > </ SOAP-ENV: Fault > ".

< Ns0: insert Xmlns: ns0 = "Http://schemas.microsoft.com/[oracledb://coredb/sharedb/tables/t_testdate]" >
< Ns0: rows >
< Ns0: insertrecord >
< Ns0: ID > 2 </ Ns0: ID >
</ Ns0: insertrecord >
</ Ns0: rows >
</ Ns0: insert >

 

 

 

The corresponding value of the data1 field is not displayed in the message.

4. Change the ing

 

 

If the value of name is of incorrect date type, an error is returned:

 

Code

Error Transmitting message: < SOAP-ENV: Fault Xmlns: SOAP-ENV = "Http://schemas.xmlsoap.org/soap/envelope" > < Faultcode > SOAP-ENV: Server </ Faultcode > < Faultstring > Request ID: Unknown
Exception type: A Cross Object System Conversion error occurred
Exception info:
Exception occurred:
E-CORE0055: type conversion from source to target Param failed
Call to insert @ oracledb: // coredb/sharedb/Tables/t_testdate
Implementation = oracledb: // coredb/sharedb/Tables/t_testdate

soap: // xmlns/schemas.microsoft.com/t_testdate/insertrecord:type/##/data1 = & gt; struct oracledb: // timestamp
(soap: // xmlns/schemas.microsoft.com/t_testdate/insert:element/rows == & gt; sequence & lt; struct oracledb: // coredb/sharedb/Tables/t_testdate/insertrecord & amp; & gt; )
(error occurred before call to foreign method) faultstring > SOAP-ENV: Fault >

For more information, see help and support in http://go.microsoft.com/fwlink/events.asp

< Ns0: insert Xmlns: ns0 = "Http://schemas.microsoft.com/[oracledb://coredb/sharedb/tables/t_testdate]" >
< Ns0: rows >
< Ns0: insertrecord > < Ns0: ID > 3 </ Ns0: ID > < Ns0: data1 > 33 </ Ns0: data1 >
</ Ns0: insertrecord >
</ Ns0: rows >

 

If name is null or is of the correct date type, data can be inserted normally.

 

5. Conclusion

When using BizTalk for an oracle project, the target database is an Oracle database and data exchange is performed using an oracle adapter. If the target table contains a date field that is not assigned a value for this field, the problem may occur, in addition, this problem is very confusing. It is often difficult for people to solve the problem in the ultra-long field.

Error: "[Oracle] [ODBC] numeric value out of range.", not necessarily a really long error.
When this problem occurs, perform multiple analyses. manually add a value to avoid suspension of the process.

 

 

----------------------------- Split line --------------------------