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.
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)
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
