topic Re: Talend Capabilities to Extract Oracle CLOB column to other tables in Talend Studio

Talend Capabilities to Extract Oracle CLOB column to other tables

Anonymous — Sat, 16 Nov 2024 10:42:03 GMT

Hello Experts,
We are planing to use talend to handle a JSON data extraction.
It is in data warehouse area and we are currently using Oracle 10g .
Current Scenario:
We have a table having 10 columns in Staging area . Out of these 1 column is CLOB column , where we are getting CLOB data in JSON format from some source systems.
It is easy to load other 9 columns to data warehouse .
The 10th single CLOB column contains 300+ fields , which need to extracted and loaded in to 3 different tables based on conditions .
Summery:
Stage Table (Having an Oracle CLOB column in JSON formatted data)-----> Need to Extract from 1 CLOB column to 300+ columns ------> Need to store some other defined tables.
Could someone please answer:
1. Can I extract this Oracle CLOB column (JSON_DATA) having JSON structured data to other tables. Is this possible in TALEND at all?
2. If yes , could you please provide an example to extract it.
Many Thanks.

Re: Talend Capabilities to Extract Oracle CLOB column to other tables

Anonymous — Tue, 12 Apr 2016 17:22:02 GMT

1. Yes you can, it´s possible in talend, talend speaks Java.
2. No, i cant, to timeconsuming, hire a professional

Re: Talend Capabilities to Extract Oracle CLOB column to other tables

Anonymous — Tue, 12 Apr 2016 22:34:53 GMT

To give you a bit of a helping hand, the Clob data will be returned by Talend as an Object. You will need to cast this to java.sql.Clob. You can do that in a tJavaRow if you like. Something like below (this may need tweaking as I wrote this from memory).....

output_row.clobString = ((java.sql.Clob)input_row.clob).getCharacterStream(1, ((java.sql.Clob)input_row.clob).length());

This will set the clobString column to be a String representation of the clob output. From that, you will need to use String processing to split the String into columns (or, if it holds JSON, you will need to parse that).
I recommend writing yourself a Java routine to process the clob as you wish since this will mean you can control your code in one location.

Re: Talend Capabilities to Extract Oracle CLOB column to other tables

Anonymous — Wed, 13 Apr 2016 17:36:26 GMT

Thanks all.
This casting is not working:
output_row.clobString = ((java.sql.Clob)input_row.clob).getCharacterStream(1, ((java.sql.Clob)input_row.clob).length());
I am getting following attached Error.

Once again , summery :
Stage Table (Having an Oracle CLOB column in JSON formatted data)-----> Need to Extract from 1 CLOB column to 300+ columns ------> Need to store some other defined tables

Many Thanks.

Re: Talend Capabilities to Extract Oracle CLOB column to other tables

Anonymous — Wed, 13 Apr 2016 17:49:07 GMT

Import java.sql

Re: Talend Capabilities to Extract Oracle CLOB column to other tables

Anonymous — Wed, 13 Apr 2016 20:31:38 GMT

The error suggests you are using "java.sql.CLOB". This is wrong. You need to use "java.sql.Clob". Case is important in Java.

Re: Talend Capabilities to Extract Oracle CLOB column to other tables

Anonymous — Thu, 14 Apr 2016 15:01:39 GMT

this was not working :
//output_row.JSON_DATA = ((java.sql.Clob)input_row.JSON_DATA).getCharacterStream(1, //((java.sql.Clob)input_row.JSON_DATA).length());

so I used the default code :
//Code generated according to input schema and output schema
output_row.JSON_DATA = input_row.JSON_DATA;
and now it is throwing below error :
Starting job WIP at 14:57 14/04/2016.
connecting to socket on port 4020
connected
java.io.IOException: Illegal character: <S>
java.io.IOException: Illegal character: <S>
331 milliseconds
disconnected
Job WIP ended at 14:57 14/04/2016.

Please sugest.