1

I am looking for a way to increment a date parameter each time the scheduler runs the job.
I have a procedure with one DATE parameter which is used to query a table and generate a report email. Is there a way to schedule the procedure to run daily and submit an incremented date value on each daily run? For example, job is submitted to start at 8am on Sept 1st, 2020 to run with DATE parameter = '31-AUG-2021'. I am expecting it to run on Sept 2nd with DATE parameter = '01-SEP-2021'. I tried this and seems it is only submitting the same value on each run.

Any suggestions or advice would be much appricated. Thank you.

DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(
                    job_name => vJobName,
                    argument_position => 1,
                    argument_value => sys.anydata.convertDate(trunc(sysdate)- trunc(vJobStartDate) + to_date(vDateArgumentValue,'dd-MON-yyyy'))
CLB_LL
  • 11
  • 1
  • Subtract one day from sysdate – astentx Sep 03 '21 at 18:33
  • Can you change the job type from a procedure to a PLSQL_BLOCK that calls the procedure, like `begin proc_name(sysdate-1); end;`? Then in the PL/SQL block, the SYSDATE expression will be re-evaluated each time the block runs. – Jon Heller Sep 04 '21 at 05:18

0 Answers0