Oracle SQL Developer dynamic date table name

I’m hoping to dynamically reference last Fridays date in the weekly sales tables in Oracle SQL Developer i.e. SELECT * FROM Sales_DDMMYY

I can do this in SQL Server (DECLARE / SET / EXECUTE) but haven’t had any joy with SQL Developer.

Even the ability to create a date variable to be referenced within the code would be a great start.

Answer

Stop!

I strongly suggest you not to do that. That’s not the way to create a data model. If you have a table which contains values related to different dates, then date should be a column in that table, such as

create table sales
  (id      number,
   datum   date,
   amount  number
  );

Insert rows as

insert into sales (id, datum, amount) 
  select 1, date '2020-06-01', 100 from dual union all
  select 2, date '2020-05-13', 240 from dual union all
  select 3, date '2020-05-13', 160 from dual;

and use it as

select sum(amount)
from sales
where datum = date '2020-05-13'

That is the way to do it. Naming columns by dates is … well, close to a suicide.

---

Aha, now I see: it is a table name that contains dates. Doesn’t really matter, my suggestion still stands. Do not do that. Use a date column within a single table.

If you want – and if you can afford it – partition the table on date value. Note that partitioning option exists in Oracle Enterprise Edition which is quite expensive. So – date column it is.

---

If there’s nothing you can do about it, then dynamic SQL it is. For example:

Sample table:

SQL> create table sales_200620 as select * From dept;

Table created.

Function that accepts ddmmyy value as a parameter, composes table name and returns a refcursor:

SQL> create or replace function f_test (par_ddmmyy in varchar2)
  2    return sys_refcursor
  3  is
  4    l_table_name varchar2(30) := 'sales_' || par_ddmmyy;
  5    l_rc sys_refcursor;
  6  begin
  7    open l_rc for 'select * from ' || dbms_assert.sql_object_name(l_table_name);
  8    return l_rc;
  9  end;
 10  /

Function created.

Testing:

SQL> select f_test('200620') from dual;

F_TEST('200620')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


SQL>

---

Without creating a function: use substitution variable:

SQL> select * From &tn;
Enter value for tn: sales_200620

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>