topic Re: Nested table Data reterival with TOracleInput in Talend Studio

Nested table Data reterival with TOracleInput

Anonymous — Sat, 16 Nov 2024 06:03:12 GMT

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

Re: Nested table Data reterival with TOracleInput

Anonymous — Wed, 17 Apr 2019 18:16:59 GMT

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.

Re: Nested table Data reterival with TOracleInput

Anonymous — Wed, 24 Apr 2019 08:41:47 GMT

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

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

Re: Nested table Data reterival with TOracleInput

Anonymous — Wed, 24 Apr 2019 10:31:08 GMT

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.

Re: Nested table Data reterival with TOracleInput

Anonymous — Fri, 26 Apr 2019 08:03:07 GMT

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

Re: Nested table Data reterival with TOracleInput

akumar2301 — Fri, 26 Apr 2019 09:26:15 GMT

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