How to get table object by name? (Oracle SQL)

Question

I was trying to find a specific subset of result tables in my database by querying for tables by name and then querying inside those tables.

I know I can get a list of tables like this:

SELECT table_name FROM all_tables WHERE table_name LIKE 'MY_RESULTS_%'

But what I can't figure out is how to use that list in another select statement. For example, I'd like to do something like this:

-- DOESN'T WORK --
SELECT table_name FROM all_tables
WHERE table_name LIKE 'OUTPUTDATA_%'
AND get_table_by_name(table_name).my_value_col = 'Special Value';

This is on an Oracle database with SQL Developer.

Not quite sure, do you mean [something like this](http://stackoverflow.com/a/12373271/266304)? You can't use a value from one table as the name of another table in plain static SQL, you'll need to use dynamic SQL in some form. — Alex Poole, Jan 28 '15 at 23:11
I think it is possible in SQL, something like this http://stackoverflow.com/a/27794127/3989608 — Lalit Kumar B, Jan 29 '15 at 03:35

score 4 · Accepted Answer · edited Jan 29 '15 at 16:38

Uese Dynamic SQL with a cursor:

DECLARE
  CURSOR all_tables IS
    SELECT table_name
            FROM all_tables 
            WHERE TABLE_NAME like 'MY_RESULTS_%' 
            ORDER BY TABLE_NAME ;
row_count pls_integer;
BEGIN
  FOR rslt IN all_tables LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' 
     || rslt.TABLE_NAME || ' where ' 
     ||rslt.TABLE_NAME||'.my_value_col= ''Special Value''' INTO row_count;
  --here if the specific table.specificcolumn has the specific value print it  
  if row_count>=1 THEN
     BEGIN
       DBMS_OUTPUT.PUT_LINE(rslt.TABLE_NAME);
     END;
  END IF;
  END LOOP;
END;

Awesome! I had to make a couple minor corrections to the script (changes made above). But that did it. Thanks! — Didjit, Jan 29 '15 at 16:22

How to get table object by name? (Oracle SQL)

1 Answers1