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

Submitting dbms_job

DECLARE
    x   NUMBER;
BEGIN
    DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'wb_donors_elig_for_conv_proc;'
   ,next_date =>  to_date('31-Jan-2014 06:30','dd-Mon-yyyy hh24:mi')
   ,interval  => 'SYSDATE + 1'
   ,no_parse  => FALSE
  );
    COMMIT;
END;

It will start every day at 6.30a.m. Is there a way to exclude sundays? Help appreciated.
0
anumoses
Asked:
anumoses
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
If you can, use dbms_scheduler instead of dbms_job.  It is much more flexible.

You can create your own function that returns the 'next' day to run and just have it skip Sunday.

A previous question has an example of using a user-defined function with dbms_job:
http://www.experts-exchange.com/Database/Oracle/Q_24518466.html


>>It will start every day at 6.30a.m.

Note:  Not specifying the EXACT time and using something like sysdate+1 will cause time drift.  It will not be exactly 'tomorrow' at 6:30 AM.

Set up a simple job that does nothing but insert sysdate into a table and test it...
0
 
anumosesAuthor Commented:
With oracle version 9 , I cannot use scheduler.
0
 
sdstuberCommented:
try using this for your interval, this will skip Sundays and eliminates the schedule drift mentioned above


interval => 'TRUNC(CASE WHEN TO_CHAR(SYSDATE, ''Dy'') = ''Sat'' THEN SYSDATE + 1 ELSE SYSDATE END) + 1 + 6.5 / 24'
0
 
anumosesAuthor Commented:
Thanks a lot.
0
 
anumosesAuthor Commented:
thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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