Skip to main content
Question

How to find tables that has common id from oracle database ?


gogopotter90
Contributor
Forum|alt.badge.img+13

Hello ,

i have around 800 tables at my oracle database and I would like to ask if there is a method that I can find which tables has the same object id .

i think ,if I read each table then use feature merger between one table and others and so on .it will take time

is there a quick method that compare between all tables and eachother to find which tables has common id and show me the name of tables that has common id.

my target to find the name of tables that has common id.

FME 2021

 

 

3 replies

jdh
Contributor
Forum|alt.badge.img+28
  • Contributor
  • May 2, 2023

I would absolutely try to keep the querying within Oracle, rather than read all the data in FME, though I'm not sure how to construct a SQL query with 800 tables

 


ctredinnick
Supporter
Forum|alt.badge.img+18
  • Supporter
  • May 4, 2023

In one SQLExecutor, select all tables which have a column named 'OBJECTID'. You can find this in the database schema tables (In SQL Server this would be in sys.columns or INFORMATION_SCHEMA.COLUMNS) I'm unfamiliar with Oracle but should be something like

 

select owner, table_name, column_name

from all_tab_columns

where column_name = 'OBJECTID'

 

That should give you your 800 table names which could have the OBEJCTID you're looking for. From it, you can create 800 SQL statements to pass into a second SQLExecutor

 

select OBJECTID

from @Value(table_name)

where OBJECTID = @Value(Search_OBJECTID)


gogopotter90
Contributor
Forum|alt.badge.img+13
  • Author
  • Contributor
  • May 5, 2023
ctredinnick wrote:

In one SQLExecutor, select all tables which have a column named 'OBJECTID'. You can find this in the database schema tables (In SQL Server this would be in sys.columns or INFORMATION_SCHEMA.COLUMNS) I'm unfamiliar with Oracle but should be something like

 

select owner, table_name, column_name

from all_tab_columns

where column_name = 'OBJECTID'

 

That should give you your 800 table names which could have the OBEJCTID you're looking for. From it, you can create 800 SQL statements to pass into a second SQLExecutor

 

select OBJECTID

from @Value(table_name)

where OBJECTID = @Value(Search_OBJECTID)

Thanks for ur idea


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings