Can I create parameterized views in oracle

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   

This query return the data for the previous month, i.e. the month befort the current month at the time of the query (= sysdate).

You use the trunc with 'MM' to get the months first and the arithmetic with add_months

SELECT ID,REF_ID,BATCHNO FROM reporttbl
where POSTING_DT >= add_months(trunc(sysdate,'MM'),-1) and POSTING_DT <  trunc(sysdate,'MM')

Another way to do this is using a function that retrieves the parameters from a table, thereby you don't need to manipulate any DDL. The idea here is

• Using a table to store the parameters, basically you need parameter value and a parameter description.
• Using a function to retrieve the value of that parameter when the input is the parameter name
• Using the function call inside the view.
• You can then manipulate the view automatically by modifying the values of the parameter table.

Table

create table my_param_table 
( param_description varchar2(100) , 
  param_value varchar2(100),
  enabled     varchar2(1)
) ;

Function

create or replace function f_retr_param  ( p_value in varchar2 )
return varchar2
is 
declare
v_value my_param_table.value_param%type;
begin
  select value into v_value from my_table_of_parameters 
  where upper(value_param) = upper(p_value) ;
  return v_value;
  exception when others then raise;
end;
/ 

View

create or replace force view my_view as 
SELECT ID,REF_ID,BATCHNO FROM reporttbl
where POSTING_DT >= f_retr_param ( p_value => 'p_start_date' );
and POSTING_DT <= f_retr_param ( p_value => 'p_end_date' );

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