CLOB transfer into String/VARCHAR2

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

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