Nested table Data reterival with TOracleInput

[Anonymous]

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 

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

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. 

[Anonymous]

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

[akumar2301]

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