CODEWITHSUNDEEP
oracle-databasedbms-schedulerdbms-job
jiii
jiii

Reputation: 61

DBMS_JOB.SUBMIT interval interpretation

I want to understand a dbms_job.submit statement

  dbms_job.submit(jobno, 'xxxsome_pl_sql_statement',next_date,interval);

next_date evaluates to Last_Day(Sysdate) ----30-apr-22

interval evaluates to Last_Day(Add_Months(Sysdate,1)) ------31-may-22

sysdate for today is 13-apr-22

1.How to interpret 31-may-22 as the interval? Should I interpret the interval as the time between Last_Day(Sysdate) and Last_Day(Add_Months(Sysdate,1)),which is approximately one month?

2.The next date to run the job has already been set, why do we need to set the interval again?

Upvotes: 0

Views: 1694

Answers (1)

pmdba
pmdba

Reputation: 7033

NEXT_DATE is the next time the job should execute. INTERVAL is a SQL formula in varchar2 format to calculate subsequent executions, not a date itself, and should be enclosed in single quotes like the PL/SQL statement.

dbms_job.submit(jobno, 'some_pl_sql_statement',Last_Day(Sysdate),'Last_Day(Add_Months(Sysdate,1))');

LAST_DAY and ADD_MONTHS are SQL functions.

DBMS_JOB.SUBMIT( 
   job       OUT    BINARY_INTEGER,
   what      IN     VARCHAR2, 
   next_date IN     DATE DEFAULT SYSDATE, 
   interval  IN     VARCHAR2 DEFAULT 'NULL',
   no_parse  IN     BOOLEAN DEFAULT FALSE,
   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,
   force     IN     BOOLEAN DEFAULT FALSE);

dbms_job.submit(
  what=>'some_plsql_statement;',
  next_date=>Last_Day(Sysdate), -- last day of this month
  interval=>'Last_Day(Add_Months(Sysdate,1))');  -- Last day of the next month after each execution

Upvotes: 0

Related Questions