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

Breadcrumb

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

Version: 23.1.0.097

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 
begin
  return ' select count(*) from tab ';
end;
/

select * from num_rows ( hr.employees );

  COUNT(*)
----------
       107

select * from num_rows ( hr.departments );

  COUNT(*)
----------
        27


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:

https://blogs.oracle.com/datawarehousing/post/sql-macros-have-arrived-in-autonomous-database
https://livesql.oracle.com/apex/livesql/file/tutorial_KQNYERE8ZF07EZMRR6KJ0RNIR.html

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here