Nested table Data reterival with TOracleInput

Seven Stars

Nested table Data reterival with TOracleInput

Hi Team,

 

Our requirement is to read data from nested tables and create csv files however nested table data is not completely fetching in talend with ToracleInput component and Dynamic data  type (Because we need to process n number of tables in list).We are using following query to fetch data from nested table.

 

SELECT * FROM XT_ST, table(OB_ST);

Csv file is not having data of nested table object.

 

Can Talend retrieve complete data from nested table?

 

Regards,

Vivek 

Community Manager

Re: Nested table Data reterival with TOracleInput

What results are you expecting and what results are you getting?

 

I think you are going to have to treat your nested table like it is multiple tables. If your data looks like this....

 

Id Name Address
1 Richard {"1 Johns Close", "Dartford", "London"}
2 John {"Home"}
3 Mike {"2 Joy Road", "Surrey","UK"}
4 Gemma {"Some", "Road", "Somewhere"}

 

I think you are going to have to work with it so that it arrives in CSV like this......

 

Id Name Address
1 Richard 1 Johns Close
1 Richard Dartford
1 Richard London
2 John Home
3 Mike 2 Joy Road
3 Mike Surrey
3 Mike UK
4 Gemma Some
4 Gemma Road
4 Gemma Somewhere

 

You can potentially group it in CSV all on one line, but I think you need to think of this like two tables. One table which holds your Id and Name and another that holds your Address details and is linked by Id. I am not certain, but this is best guess as to a solution to the problem I *think* you have.

Seven Stars

Re: Nested table Data reterival with TOracleInput

Looking for appropiate solution .how to dump the nested table data into csv file in talend.

or

any other way to create dump file(.dmp) of nested tables in talend?

Community Manager

Re: Nested table Data reterival with TOracleInput

I think you need to write a query that will output your data in the format I represent in the second of the two tables I put together above. 

Seven Stars

Re: Nested table Data reterival with TOracleInput

I checked that but its not working.Is there way way in Talend to achieve this ?

Eleven Stars

Re: Nested table Data reterival with TOracleInput

@vivek_u

 

I followed below link to create NESTED TABLE 

https://www.orafaq.com/wiki/NESTED_TABLE

 

then in tOracleInput , I used below query

 

"select c.custid, u.street ,u.city,u.state,u.zip
from customers c, table (c.address) u "

 

I was able to create CSV file.

Regards
Abhishek KUMAR

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now