Issues When Trying to Load XML Message From Jms Queue To Oracle Table As Xmltype
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***
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
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.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):
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.
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