topic Re: CLOB transfer into String/VARCHAR2 in Talend Studio

CLOB transfer into String/VARCHAR2

Anonymous — Sat, 16 Nov 2024 14:15:02 GMT

I am transfering data between two 10G oracle databases.
The CLOB columns must be transferred into VARCHAR2(500) columns.
I am using the tmap component.
The CLOB columns are OBJECT type in the tmap. Can I apply a java function to transform them into string?
Thanks.

Re: CLOB transfer into String/VARCHAR2

Anonymous — Wed, 27 Aug 2008 20:05:24 GMT

Error meesage:
Exception in component tOracleInput_3
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
at oracle.jdbc.driver.CharCommonAccessor.getInt(CharCommonAccessor.java:140)
at oracle.jdbc.driver.T4CVarcharAccessor.getInt(T4CVarcharAccessor.java:779)
at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:712)
disconnected
at oltptranfer.transfer_webdevtoinfdev_0_1.Transfer_WEBDEVtoINFDEV.tOracleInput_3Process(Transfer_WEBDEVtoINFDEV.java:4576)
at oltptranfer.transfer_webdevtoinfdev_0_1.Transfer_WEBDEVtoINFDEV.runJobInTOS(Transfer_WEBDEVtoINFDEV.java:5178)
at oltptranfer.transfer_webdevtoinfdev_0_1.Transfer_WEBDEVtoINFDEV.main(Transfer_WEBDEVtoINFDEV.java:5037)

Re: CLOB transfer into String/VARCHAR2

Anonymous — Tue, 02 Sep 2008 05:08:23 GMT

Hi,
I have the same issue as well. When trying to transfer a column of CLOB type to VARCHAR2. I am using the query to convert CLOB to VARCHAR and insert into the output oracle db. The following query runs well when I test it in SQL builder in TalenD but when I transfer I am getting the error message "Failed to convert to internal representation". Can you please look into this issue? We have been struck with this for quite some time now.
Thanks in advance.
SELECT TO_CHAR(i.item_text) as item_text,
TO_CHAR(ss.subitem_text) as subitem_text,
TO_CHAR(p.page_introduction) as page_introduction,
TO_CHAR(i.other_text) as other_text,
TO_CHAR(ia.answer_text) as answer_text,
trim(ia.answer_value) answer_value

FROM SUR_SURVEY_TO_ITEM_MAPPING m, SUR_ITEM i, SUR_PAGE p,
SUR_ITEM_ANSWER ia, sUR_SUBITEM ss
WHERE m.item_id = i.item_id
AND m.item_id = ia.item_id (+)
AND m.item_id = ss.item_id (+)
AND m.survey_id = p.survey_id (+)
AND m.page_number = p.page_number (+)
ORDER BY m.page_number, m.order_number, ss.order_number,ia.order_number

Re: CLOB transfer into String/VARCHAR2

_AnonymousUser — Mon, 23 Jan 2012 12:33:17 GMT

I tried that but you are limited to a VARCHAR of 4000 and my CLOB is bigger than that.