Search code examples
oracle-databaseviewparameterized

Can I create parameterized views in oracle

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.

Solution

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