Inserting into Oracle Xmltype column with more than 4000 characters

Anonymous — Fri, 27 Apr 2012 09:59:00 GMT

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:
https://community.talend.com/t5/Design-and-Development/Problem-in-Inserting-the-XML/td-p/114288
https://community.talend.com/t5/Design-and-Development/Bulk-Load-to-Oracle-XML-input-instead-of-CSV/td-p/106126
https://community.talend.com/t5/Design-and-Development/Storing-and-XMLType-object-in-Oracle/td-p/111132
https://community.talend.com/t5/Design-and-Development/Extract-Information-from-a-XML-File-which-is-stored-as-a-row-in/td-p/87331
Can anyone help me? Any workaround? Is it possible at all in Talend?
Regards,
Adam

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

Anonymous — Fri, 27 Apr 2012 10:58:09 GMT

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

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

Anonymous — Mon, 30 Apr 2012 12:50:55 GMT

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

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

Anonymous — Wed, 02 May 2012 07:42:58 GMT

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

topic Inserting into Oracle Xmltype column with more than 4000 characters in Talend Studio

Inserting into Oracle Xmltype column with more than 4000 characters

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

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

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