Querying an Oracle Database with Dynamic Table names

Question

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.

SELECT
  ajob.ORDER_ID
     , ajob.JOB_NAME
     , abim.SERVICE_ID
     , shist.SERVICE_NAME
  FROM
    obscuredschema.A190129001_AJOB               ajob --hardcoded YYMMDD table name
    INNER JOIN obscuredschema.A190129001_ABIMSVC abim --hardcoded YYMMDD table name
               ON (ajob.ORDER_ID = abim.ORDER_ID)
    INNER JOIN obscuredschema.SERVICE_HIST       shist
               ON (abim.SERVICE_ID = shist.SERVICE_KEY)
WHERE shist.SERVICE_NAME LIKE '%BIM'
  AND shist.BIM_AUTH_ID > 0
;

Noting the two hardcoded table names (along with aliases)

How would I execute this same query using dynamic table names? (There's two)

The code for the dynamic date: TO_CHAR(trunc(sysdate - 7), 'YYMMDD')

If the first table name were a string, here's how I would build it: 'A'||TO_CHAR(trunc(sysdate - 7), 'YYMMDD')||'001_AJOB'

If the second table name were a string, here's how I would build it: 'A'||TO_CHAR(trunc(sysdate - 7), 'YYMMDD')||'001_ABIMSVC'

Answer 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.