Issues When Trying to Load XML Message From Jms Queue To Oracle Table As Xmltype (Doc ID 1139775.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Data Integrator - Version 10.1.3.5.6 and later
Information in this document applies to any platform.
***Checked for relevance on 01-07-2014***

Symptoms

A user is trying to load XML messages from IBM MQ to Oracle as Xmltype. The following is the details:

Source: IBM MQ with xml messages as data defined in ODI as a JMS Queue Datastore with one column String 6000
Record Seperator: ACAC
Field Seperator: 24A324

Target: Oracle the target column is Xmltype

Mapping: XMLTYPE(JMS_Q.COL)

executed on target Oracle.

In Topology>JMS>datatypes>String>Converted to for Oracle is changed from the default VARCHAR2 to CLOB

(as with VARCHAR2 the C$ table creates the column as VARCHAR2 and it fails with:

1461 : 72000 : java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column
1461 : 72000 : java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

)

When the Interface runs, it fails with the following at Insert New Rows step (that step that inserts to target):

31011:99999:java.sql.SQLExcecption:ORA-31011: XML parsing failed
ORA-19202:Error occured in XML processing
LPX-00245:extra data afer end of document
Error at line 11
ORA-06512:at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

It seems while transforming the String from the JMS Queue into a XML using Oracle's XMLTYPE() function, it failed parsing the source String.

If you keep the C$ table created, you will see data is loaded to the C$ (as in C$ the datatype is CLOB not XMLTYPE and is not parsed there). However if looking at the data in the C$ table, at the end of the XML, some characters that look like: ï¿1/2 ï¿1/2 are seen.

These characters are causing the above error.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms