Can I create parameterized views in oracle

Question

I have a query like this

SELECT ID,REF_ID,BATCHNO FROM reporttbl
where POSTING_DT >= '06/01/2020' and POSTING_DT <= '06/30/2020'

and I need it every month, so I would like to put it in a view, but as the date changes every month, it would be great to have a date parameter that I can pass to the view when calling it. Is there a way on how can i achieved this? I'm new to oracle, appreciate every help. Thank youu.

Answer 1

From 19.6 you can create parameterized views using SQL macros.

create or replace function get_month (
  tab dbms_tf.table_t, start_date date, end_date date
) return varchar2 sql_macro as
  retval int;
begin
  return 'select * from tab 
    where dt >= start_date and dt < end_date + 1';
end get_month;
/

create table t (
  c1 int, dt date
);

insert into t 
with rws as (
  select level c1, add_months ( date'2019-12-25', level ) dt 
  from   dual
  connect by level <= 10
)
  select * from rws;
  
select * from get_month ( 
  t, date'2020-06-01', date'2020-07-01' 
);

C1    DT                     
    6 25-JUN-2020 00:00:00    

select * from get_month ( 
  t, date'2020-08-01', date'2020-09-01' 
);

C1    DT                     
    8 25-AUG-2020 00:00:00   

Answer 2

There are ways to "parameterize" a view e.g. using Oracle contexts, but they aren't often useful and certainly not for your case.

If your query really just selects from one table with just the dates as predicates then a view doesn't add much value either. You could create a SQL script (in a file e.g. myquery.sql) using bind variables:

SELECT ID,REF_ID,BATCHNO FROM reporttbl
where POSTING_DT >= to_date(:from_date) and POSTING_DT <= to_date(:to_date);

Then every month you can just open the file and run it, and it will prompt you for the 2 dates. Or you can run as a script like this and it will also prompt you:

@myquery.sql

Or if you use substitution strings '&1.' and '&2.' instead:

SELECT ID,REF_ID,BATCHNO FROM reporttbl
where POSTING_DT >= to_date('&1.') and POSTING_DT <= to_date('&2.');

Then you can pass the dates in on the command line like this:

@myquery '06/01/2020' '06/30/2020'

(Because &1. means first parameter on command line, etc.)