One Star

CLOB transfer into String/VARCHAR2

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.
3 REPLIES
One Star

Re: CLOB transfer into String/VARCHAR2

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)
One Star

Re: CLOB transfer into String/VARCHAR2

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
One Star

Re: CLOB transfer into String/VARCHAR2

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