Querying An Oracle Database With Dynamic Table Names

I'm stuck with some poor database design where I have to query tables that are named by date. The following query works when the table names are hard coded with relevant dates. SEL

Solution 1:

I don't think you can write a plain SQL query with dynamic table names.

You can write a PL/SQL procedure which uses execute immediate and returns a cursor or something; somebody asked about that just yesterday. If you're just trying to write this query to interact with some data, that might be your best bet.

In addition, you could modify that by turning your PL/SQL procedure into a pipelined function, and then you could call it from a SQL query using TABLE().

If it were me, I'd consider creating a synonym (or a standard view which just selects from the dynamically-named-tables), and scheduling a job to re-create it every time new tables are created. That might be simpler than dealing with pipelined functions.