perform select count(*) from table (oracle ) in talend

Six Stars

perform select count(*) from table (oracle ) in talend

I want to get all row count of table. 

Select count(*) from table;

and i want to use this count further in code.

 

use case:

i have 180 tables in db.

i want to get count of all 180 tables one by one.


Accepted Solutions
Six Stars

Re: perform select count(*) from table (oracle ) in talend

Hi Yogesh,

 

You can do this couple of ways depending on how the output is going to be used.

 

Add a tOracleInput with following SQL 

SELECT table_name
FROM all_tables
WHERE owner='schema_name'
ORDER BY table_name

 

2) Send this to tFlowToIterate and set a Variable 

3) Add another tOracleInput with Dynamic SQL to get Rowcount. 

4) Store it for further use.

See the screenshot attached. 

Oracle_Rowcounts.jpg

 

 

 

Or 

You can always build an SQL Output with Union Statement that has all 180 Tables and just use that as a SQL for single tOracleInput downside to that is that it won't be dynamic anymore. 

 

Hope this helps. 

Datta.


All Replies
Nine Stars

Re: perform select count(*) from table (oracle ) in talend

You can achive this by using tOracleInput, tJavaRow and tMap.

TableRecordCnt.PNG

 

Read table name using tOracleInput any other source. assign table name to context variable using tJavarow.

Use that context variable in another tOracleInput to get record count.

TableRecordcnt1.PNGTableRecordcnt2.PNGTableRecordcnt3.PNG

Regards,

Veeru Boppudi
Six Stars

Re: perform select count(*) from table (oracle ) in talend

Hi Yogesh,

 

You can do this couple of ways depending on how the output is going to be used.

 

Add a tOracleInput with following SQL 

SELECT table_name
FROM all_tables
WHERE owner='schema_name'
ORDER BY table_name

 

2) Send this to tFlowToIterate and set a Variable 

3) Add another tOracleInput with Dynamic SQL to get Rowcount. 

4) Store it for further use.

See the screenshot attached. 

Oracle_Rowcounts.jpg

 

 

 

Or 

You can always build an SQL Output with Union Statement that has all 180 Tables and just use that as a SQL for single tOracleInput downside to that is that it won't be dynamic anymore. 

 

Hope this helps. 

Datta.