I am going to assume that you want to get every row in every table that has the column CITY in it with a value of "Los Angeles." I am also assuming that you do not care about whether there is any other relationship that needs to be satisfied. This type of requirement is prevalent in a data warehouse scenario and one way to do it is through what we call a Cartesian product. However, you may also want to individually query each table. Now, if you do not know which tables have the column CITY in it, then you can use the data dictionary view USER_TAB_COLUMNS. You can also dynamically create your query as follows using this data dictionary view.
SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'CITY'
You can dynamically create the query that will retrieve the rows from all the tables having a column named CITY with a value of Los Angeles using two queries. The first query will create the SELECT * CLAUSE and second query will create the where clause. Combining the result sets using an editor you can get your final SQL statement.
Let's say that there are five tables in your database with column CITY with the following names:
In the queries below I have used the function CHR to print the single quote whose ASCII value is 39. I need this quote as part of my result. The Quotes around Los Angeles is to treat Los Angeles as string literal for the concatenation purpose below. I also used the UPPER built-in function. You may or may not need to use it.
Query 1: Set heading off Set pagesize 200 SELECT DECODE(rownum, 1, 'SELECT * from ' || table_name || ' T'|| rownum, ',' || table_name || ' T'|| rownum) col_val FROM user_tab_columns where column_name = 'CITY' ORDER BY ROWNUM
This query will result in the following output
SELECT * from TABLE_1 T1 ,TABLE_2 T2 ,TABLE_3 T3 ,TABLE_4 T4 ,TABLE_5 T5 Query 2: SELECT DECODE(rownum, 1, 'WHERE UPPER(T'|| rownum || '.' || 'CITY) = ' || CHR (39) || 'LOS ANGELES' || CHR(39) , 'OR UPPER(T'||rownum || '.' || 'CITY) = ' || CHR(39) || 'LOS ANGELES '|| CHR (39)) col_val FROM user_tab_columns where column_name = 'CITY' ORDER BY ROWNUM
This query will give the following output.
WHERE UPPER(T1.CITY) = 'LOS ANGELES OR UPPER(T2.CITY) = 'LOS ANGELES OR UPPER(T3.CITY) = 'LOS ANGELES OR UPPER(T4.CITY)= 'LOS ANGELES OR UPPER(T5.CITY) = 'LOS ANGELES
You cannot use a UNION in this case at all as the output may not be in the order you want and "order by" clause is not allowed in UNION. Therefore, you will have to combine the two query results to get your final query. Please make sure you contact your DBA and confirm whether it is okay to run a query that results in a Cartesian product. Because I do not know how many tables in your database have the column CITY; nor do I know the size of these tables that you will be using to perform this query.
In that case you may want to run the queries separately. If you want to dynamically create the separate queries, run the following SQL statement
Set heading off; SELECT 'SELECT * FROM ' || TABLE_NAME || ' WHERE UPPER(CITY) = ' || CHR(39) || 'LOS ANGELES' || CHR(39) || ';' from user_tab_columns where column_name = 'CITY'
The result of this query will be
SELECT * FROM TABLE_1 WHERE CITY = 'LOS ANGELES'; .. .. SELECT * FROM TABLE_5 WHERE CITY = 'LOS ANGELES';
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Azim Fahmi
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.