Skip to Main Content
  • Questions
  • Is 'SELECT * FROM :TABLE_NAME;' available?


Question and Answer

Chris Saxon

Thanks for the question, Batzorig.

Asked: April 08, 2024 - 7:13 am UTC

Last updated: April 09, 2024 - 12:47 pm UTC


Viewed 1000+ times

You Asked

Is 'SELECT * FROM :TABLE_NAME;' available?

and Chris said...

If you mean can you use bind variables for table names, the answer is no.

That said, with SQL macros you may be able to get close to the functionality you're looking for. These are functions that return query templates with table names as placeholders in the template.

When you call the macro, the database replaces the placeholder with the corresponding parameter you pass to it.

For example, here's a macro to count the rows in a table:

create or replace function num_rows (
  tab dbms_tf.table_t
) return clob sql_macro as 
  return ' select count(*) from tab ';

select * from num_rows ( hr.employees );


select * from num_rows ( hr.departments );


The table parameters must be identifiers, you can't use bind variables for these. Table macros are available from 19.6. For more on how macros work, see:

More to Explore


Complete documentation on Securefiles and Large Objects here