My Oracle Support Banner

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

Last updated on OCTOBER 29, 2020

Applies to:

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


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 Separator: ACAC
Field Separator: 24A324

Target: Oracle the target column is Xmltype


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.SQLException:ORA-31011: XML parsing failed
ORA-19202:Error occurred in XML processing
LPX-00245:extra data after 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.


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.