Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE pass_expire (id, expiry_dt) AS
SELECT LEVEL, SYSDATE + LEVEL - 1
FROM   DUAL
CONNECT BY LEVEL <= 30;
30 rows affected

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
       )


ID
EXPIRY_DT


2
03-SEP-21

4
05-SEP-21

6
07-SEP-21

8
09-SEP-21

16
17-SEP-21


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


ID
EXPIRY_DT


2
03-SEP-21

4
05-SEP-21

6
07-SEP-21

8
09-SEP-21

16
17-SEP-21


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 )


ID
EXPIRY_DT


16
17-SEP-21

8
09-SEP-21

6
07-SEP-21

4
05-SEP-21

2
03-SEP-21