• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

dbms job submit

Originally it was

--dbms job run every 1st of the month for monthly proposed annual goal report

DECLARE
    x   NUMBER;
BEGIN
dbms_job.submit(job => X,
                what => 'mthly_prop_goal_imp_prc;' ,
                next_date => trunc(add_months(sysdate, 1), 'mm'),
                interval => 'trunc(add_months(sysdate, 1), ''mm'')',
                no_parse => true);
commit;
end;

I need to create a job that will run last day of the month at 11.00pm
Need help
0
anumoses
Asked:
anumoses
  • 2
1 Solution
 
johnsoneSenior Oracle DBACommented:
How long will the job run?  I believe that the next interval is set after the job completes, and if it takes longer than an hour you are in a different month so the date math changes.

If it will run greater than an hour, then use this:

DECLARE
    x   NUMBER;
BEGIN
dbms_job.submit(job => X,
                what => 'mthly_prop_goal_imp_prc;' ,
                next_date => last_day(trunc(sysdate)) + (23/24),
                interval => 'last_day(trunc(sysdate)) + (23/24)',
                no_parse => true);
commit;
end;

Open in new window


If it will run less than an hour, then use this:

DECLARE
    x   NUMBER;
BEGIN
dbms_job.submit(job => X,
                what => 'mthly_prop_goal_imp_prc;' ,
                next_date => last_day(trunc(sysdate)) + (23/24),
                interval => 'last_day(add_months(trunc(sysdate),1)) + (23/24)',
                no_parse => true);
commit;
end;

Open in new window

0
 
anumosesAuthor Commented:
job runs just  for a few seconds.
0
 
anumosesAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now