0

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?

jiii
  • 71
  • 4
  • Exactly what do you mean by "next_date evaluates to Last_Day(Sysdate)" and "interval evaluates to Last_Day(Add_Months(Sysdate,1))"? What is this "Last_Day" that you reference? Perhaps if you show the actual, exact statement you are issuing. And note this, from the "PL/SQL Packages and Types Reference, 18c" - "Note: The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package, and support for DBMS_JOB might be removed in future releases of Oracle Database." – EdStevens Apr 13 '22 at 21:58
  • yes I am showing the actual statement. dbms_job.submit(jobno, 'some_pl_sql_statement',Last_Day(Sysdate),Last_Day(Add_Months(Sysdate,1))); I am aware that dbms_job is superseded by dbms_scheduler. i wanted to understand what is happening in that dbms_job statement in order to do the migration to dbms_scheduler – jiii Apr 13 '22 at 22:04
  • dbms_job.submit(jobno, 'some_pl_sql_statement',Last_Day(Sysdate),Last_Day(Add_Months(Sysdate,1))); for this dbms job, does it mean that this pl/sql statement will run at the end of this month, and it will run every month automtically? – jiii Apr 13 '22 at 22:09
  • mea culpa. I did not recognize that LAST_DAY is actually a standard oracle function. – EdStevens Apr 14 '22 at 16:30

1 Answers1

0

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
pmdba
  • 6,457
  • 2
  • 6
  • 16