Inserting into Oracle Xmltype column with more than 4000 characters

One Star

Inserting into Oracle Xmltype column with more than 4000 characters

Hi All,
Oralce version : 11.2.0.2.0 Xe
Talend version: 5.0.1
First case
I want to transfer Xmltype data from one oracle table to another in a different schema. When the xml is smaller than 4000 char everything works fine. With Bigger xml data I become an oracle error: "ORA-01461: can bind a LONG value only for insert into a LONG column", which is misleading because no LONG column is involved(in oracle it is a character type not a number like in java).
My job looks like as follows:
tOracleInput -> tMap -> tOracleOutPut
I use the following:
1. tOracleInput query:
SELECT
T.IDNUM,
T.XMLCOL.GETCLOBVAL() XMLCOL
FROM HR.XML_TAB T WHERE IDNUM = 4
2. tOracleInput schema:
IDNUM Java Type : int Db Type: Number
XMLCOL Java Type : String Db Type: XmlType
3. tOracleInput -> Advanced Settings -> Convert XmlType to Java Type = true -> XMLCOL to String type
4.tOracleOutPut schema:
IDNUM Java Type : int Db Type: Number
XMLCOL Java Type : String Db Type: XmlType
5. I use tOracleConnection and tOracleCommit for both database schemas
6. In tMap there is only a mapping nothing changes
Second case:
I use "Object" for Java type and "XmlType" for Oracle type. This works fine within the same schema, but when I try to insert in a table in another schema (same or different Db doesn't matter) I become an error.
Differencies from the first job:
2. tOracleInput schema:
IDNUM Java Type : int Db Type: Number
XMLCOL Java Type : Object Db Type: XmlType
3. tOracleInput -> Advanced Settings -> Convert XmlType to Java Type = false
4.tOracleOutPut schema:
IDNUM Java Type : int Db Type: Number
XMLCOL Java Type : Object Db Type: XmlType
Error meassage:
Keine weiteren Daten aus Socket zu lesen -> There is no more data from socket to read
Keine weiteren Daten aus Socket zu lesenException in component tOracleCommit_2
java.sql.SQLRecoverableException: Keine weiteren Daten aus Socket zu lesen
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1053)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1010)
at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:97)
at oracle.jdbc.driver.T4CConnection.doCommit(T4CConnection.java:539)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:3432)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:3438)
at xmlbearbeitung.xmltypetest_0_1.XmlTypeTest.tOracleCommit_2Process(XmlTypeTest.java:3165)
at xmlbearbeitung.xmltypetest_0_1.XmlTypeTest.tOracleCommit_1Process(XmlTypeTest.java:3100)
at xmlbearbeitung.xmltypetest_0_1.XmlTypeTest.tPostjob_1Process(XmlTypeTest.java:3014)
at xmlbearbeitung.xmltypetest_0_1.XmlTypeTest.runJobInTOS(XmlTypeTest.java:3711)
at xmlbearbeitung.xmltypetest_0_1.XmlTypeTest.main(XmlTypeTest.java:3536)
I went through the following topics but found no solution:
http://www.talendforge.org/forum/viewtopic.php?id=22152
http://www.talendforge.org/forum/viewtopic.php?id=18530
http://www.talendforge.org/forum/viewtopic.php?id=9126
http://www.talendforge.org/forum/viewtopic.php?id=4585
Can anyone help me? Any workaround? Is it possible at all in Talend?
Regards,
Adam
One Star

Re: Inserting into Oracle Xmltype column with more than 4000 characters

Hi Adam
Please report it on BugTracker.
For this feature, there is no instruction in any document.
I tried to reproduce it and failed.
Regards,
Pedro
One Star

Re: Inserting into Oracle Xmltype column with more than 4000 characters

Hello Pedro!
Thanks for the reply. I have solved the issue as follows:
1. I created a temp table in the source schema to hold the XML, but with a CLOB column for XML storeing. (actually a key column as update key and a CLOB column are needed). For querying the source data ".getclobval()" is necessary.
2.I copied all non-XML data to the target schema in another Db and populated the Temp table.
3.I red the temp table and updated the XMLTYPE column with CLOB data. The target table has an XMLType Db column, but when the source is a real CLOB (not an XMLType converted with .getclobval() to a CLOB) it works.
4.I dropped the temp table.
This works but it is a bit to much work.
Regards,
Adam
One Star

Re: Inserting into Oracle Xmltype column with more than 4000 characters

Hi Adam
Great! Thanks for your feedback.
Regards,
Pedro

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog