SQL Oracle - How to make query dynamic to accept multiple days to calculate the expiration

Question

I want to dynamically create query to accept parameters dynamically to send notifications 1,3,5,10,15 and 20 days the password is going expire. I already went through link how to get the 30 days before date from Todays Date.

These date values must be configurable and should be passed through code to method.

I've developed a query, but is there any better ways to pass this values? How to dynamically accept the days value?

SELECT * FROM PASS_EXPIRE
WHERE TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 1) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 3) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 5)
OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 7) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 15) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 20);

You could pass a string containing a comma-separated list of day values. This answer shows how to turn that into a subquery that you can merge with your query: https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle — Erich Kitzmueller, Sep 02 '21 at 07:13

MT0 · Accepted Answer · 2021-09-02T08:06:17.203

3

You can use:

SELECT *
FROM   PASS_EXPIRE
WHERE  TRUNC(EXPIRY_DT) IN (
         WITH days (day) AS (
           SELECT  1 FROM DUAL UNION ALL
           SELECT  3 FROM DUAL UNION ALL
           SELECT  5 FROM DUAL UNION ALL
           SELECT  7 FROM DUAL UNION ALL
           SELECT 15 FROM DUAL
         )
         SELECT TRUNC(SYSDATE) + day
         FROM   days
       )

Or, use a collection (which could be passed as a bind parameter):

SELECT *
FROM   PASS_EXPIRE
WHERE  TRUNC(EXPIRY_DT) IN (
         SELECT TRUNC(SYSDATE) + COLUMN_VALUE
         FROM   TABLE(SYS.ODCINUMBERLIST(1, 3, 5, 7, 15))
       )

or you can JOIN (and, again, the collection could be passed to the query as a bind variable):

SELECT p.*
FROM   PASS_EXPIRE p
       INNER JOIN TABLE(SYS.ODCINUMBERLIST(1, 3, 5, 7, 15)) t
       ON (   p.expiry_dt >= TRUNC(SYSDATE) + t.COLUMN_VALUE
          AND p.expiry_dt <  TRUNC(SYSDATE) + t.COLUMN_VALUE + 1 )

Note: This query would allow you to use an index on expiry_dt; in contrast, comparing on TRUNC(expiry_dt) would not allow an index to be used as Oracle would need a separate function-based index on TRUNC(expiry_dt).

db<>fiddle here

edited Sep 02 '21 at 08:06

answered Sep 02 '21 at 07:47

MT0

143,790
11
59
117

Is there any reason you're sysdate - days? It should be plus right? If tomorrow 30,45 and 60 days added I should be able to add it without changing logic as such. – Jeff Cook Sep 02 '21 at 08:00
For 2nd quert: I got SQL Error [932] [42000]: ORA-00932: inconsistent datatypes: expected DATE got NUMBER – Jeff Cook Sep 02 '21 at 08:02
@JeffCook Fixed the `-`, that was just a typo. All the queries work fine in [db<>fiddle for Oracle 18](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=54b624b08ffe8fd5b324a86a1aca517f) and [db<>fiddle for Oracle 11](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=54b624b08ffe8fd5b324a86a1aca517f); if something does not work for you then please create a [MRE] with `CREATE TABLE` and `INSERT` statements (and what Oracle version you are using). – MT0 Sep 02 '21 at 08:10
You 2nd query is not working, however 3rd works fine – Jeff Cook Sep 02 '21 at 09:20
@JeffCook You can see from the linked db<>fiddles in my previous comment that it does work and I understand that you say that it does not work **for you** but just saying the "2nd query is not working" is not constructive. Please debug the issue and post more details to say why it does not work, what the error is, what the difference is between your tables and my examples and, if possible, reproduce the issue in a db<>fiddle. – MT0 Sep 02 '21 at 10:07

score 1 · Answer 2 · answered Sep 02 '21 at 07:47

I'd probably declare a collection, pass that collection to your procedure, and then use the member of operator to return only the rows from the table that match an element from the collection

create type date_tbl is table of date;

declare
  -- You'd pass in l_dates
  l_dates date_tbl := date_tbl( trunc(sysdate+1), trunc(sysdate+3), trunc(sysdate+5),
                                trunc(sysdate+10), trunc(sysdate+15), trunc(sysdate+20));
begin
  -- Your query goes here.  Use the `member of` function to restrict yourself to
  -- rows from the table that are in the collection
  for i in (select d.*
              from (select trunc(sysdate) + level dt
                      from dual
                   connect by level <= 30) d
             where d.dt member of l_dates )
  loop
    dbms_output.put_line( i.dt );
   end loop;
end;

Prints out (when run on 2021-09-02)

03-SEP-21
05-SEP-21
07-SEP-21
12-SEP-21
17-SEP-21
22-SEP-21

See this fiddle

score 0 · Answer 3 · answered Sep 02 '21 at 07:56

The idea I have is to create another table with days to expire:

days
1
3
5

and so on

and then just use cross join for retrieval passwords that are going to expire

select 'password expires in ' || ds.days || case when ds.days = 1 then ' day' else ' days' end
  from PASS_EXPIRE pe
 cross join days ds
 where pe.expiry_dt = (trunc(sysdate) - ds.days);

Thus, all you need to do in the application is to set up a dialog to maintain that table

SQL Oracle - How to make query dynamic to accept multiple days to calculate the expiration

3 Answers3

Linked