Evaluate user-defined variables in FROM and WHERE clauses (Oracle DEFINE command)

I want to use the DEFINE command in Oracle to define strings and evaluate them in the SELECT, FROM and WHERE clauses of a query.

I want to select all the credits that started in January 2018 from the tables credits_201801, credits_201802, credits_201803, credits_201804, credits_201805 and credits_201806. In other words, I want to visualize the behavior of the January 2018 credits 0, 1, 2, 3, 4 and 5 months after they started.

For example, I tried the following code to check the status of the Jan 2018 credits in Feb 2018:

DEFINE crop   = '2018-01-01';
DEFINE crop_n = ADD_MONTHS(&crop, 1); -- I want this to return 2018-01-01 plus `n = 1` months
DEFINE table_ = REPLACE(SUSBTR(&crop_n, 1, 7), '-'); -- I want this to return '201802'

SELECT
    PERSON_ID,
    BANK_ID,
    ACCOUNT_ID,
    START_DATE,
    MONTHS_BETWEEN(&crop_n, &crop) AS PERIOD,
    DAYS_OVERDUE
FROM
    'MDB.CREDITS_' || &table_ -- I want this to be evaluated as 'MDB.CREDITS_201802'
WHERE
    START_DATE >= &crop -- Credits that started on or after Jan 1 2018
    AND START_DATE < ADD_MONTHS(&crop, 1) -- Credits that started before Feb 1 2018

The user-defined variables are working well in the SELECT clause, but it seems that the FROM section is evaluating the table as a literal string rather than the name of the table. The WHERE clause is not working either.

I specifically need used DEFINE because it’s the only command I know of that doesn’t require me to use INSERT INTO (I am only allowed to select).

Answer

A date is written like date '2018-01-01', not '2018-01-01'.

define expects a single value, so if you want it to include blank spaces then you’ll need to quote it.

To construct a string in YYYYMM format from a date, you need to use to_char(dateval,'YYYYMM').

You also need to evaluate the table_ expression so you can substitute a literal string into the table name in the final query.

define crop   = "date '2018-01-01'"
define crop_n = "add_months(&crop, 1)" -- I want this to return 2018-01-01 plus `n = 1` months

column table_ new_value table_

select to_char(&crop_n,'YYYYMM') as table_ from dual;

prompt crop   = &crop
prompt crop_n = &crop_n
prompt table_ = &table_

select person_id
     , bank_id
     , account_id
     , start_date
     , months_between(&crop_n, &crop) as period
     , days_overdue
from   MDB.CREDITS_&table_ -- I want this to be evaluated as 'MDB.CREDITS_201802'
where  start_date >= &crop -- Credits that started on or after Jan 1 2018
and    dcredfaperturacta < add_months(&crop, 1) -- Credits that started before Feb 1 2018
/

The column x new_value y setting captures the last value of column x into substitution variable y. It’s originally meant for page headers in reports, but very handy for scripting. You can hide the output using set termout off. (set termout on to restore terminal output.)

This will generate and run

crop   = date '2018-01-01'
crop_n = add_months(date '2018-01-01', 1)
table_ = 201802

select person_id
     , bank_id
     , account_id
     , start_date
     , months_between(add_months(date '2018-01-01', 1), date '2018-01-01') as period
     , days_overdue
from   MDB.CREDITS_201802 -- I want this to be evaluated as 'MDB.CREDITS_201802'
where  start_date >= date '2018-01-01' -- Credits that started on or after Jan 1 2018
and    dcredfaperturacta < add_months(date '2018-01-01', 1) -- Credits that started before Feb 1 2018

Note following comments: this is Oracle SQL*Plus syntax. Other tools such as Toad, PL/SQL Developer and SQL Developer support it to some extent and using different methods. Not all SQL*Plus syntax is guaranteed to work in all other tools. In PL/SQL Developer it only works in a Command Window. In SQL Developer you will need to use “Run as Script” (F5) and not the regular “Run” (Ctrl-Enter).