Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBMS_JOB

Re: DBMS_JOB

From: Tim Hall <timhall1_at_gmail.com>
Date: Wed, 2 Mar 2005 13:32:47 -0800
Message-ID: <6043f371050302133246caa9d@mail.gmail.com>


I don't have time to test this, or fix parentheses errors on my CASE statement, etc., but how about something like:

(CASE WHEN TO_CHAR(SYSDATE+(1/48),'HH24') BETWEEN '01' AND '12' THEN (TRUNC(SYSDATE)+(25/48))
ELSE (TRUNC(SYSDATE -(1/24))+(49/48)) END) So if the current time is between 00:30:00 and 12:29:59, then the job runs at 12:30 in the afternoon today,
else if the current time is not between 00:30:00 and 12:29:59, then the job runs at 12:30 at night (same day if current time is between 00:00:00 and 00:29:59, next day if current time is between 12:30:00 and 23:59:59)

Hope this helps -- Tim

On Wed, 2 Mar 2005 16:19:48 -0500, Goulet, Dick <DGoulet_at_vicr.com> wrote:
> Steve,
>
> Have similar, but not identical problems. We want to skip over
> weekends. The interval parameter only needs to evaluate to a date data
> type, therefore the solution here has been to use the decode function.
> It works well, although how you'd use it, I'm not 100% sure. Need to
> look in a book & experiment a touch.
>
> -----Original Message-----
> From: Dirschel, Steve [mailto:Steve.Dirschel_at_bestbuy.com]=20
> Sent: Wednesday, March 02, 2005 4:06 PM
> To: oracle-l_at_freelists.org
> Subject: DBMS_JOB
>
> Oracle 9.2
>
> We want to schedule a job via DBMS_JOB to run at 12:30am and 12:30pm.
> I'd prefer to schedule 1 job to do this rather than 1 job to run at
> 12:30am and 1 job at 12:30pm (I don't want them to run on top of each
> other).
>
> When I schedule the job via this command:
>
> execute dbms_job.submit(job=3D3D>:jobno, what=3D3D>'program_name;',
> next_date=3D3D>trunc(sysdate+1)+1/48,
> interval=3D3D>'trunc(SYSDATE+12/24,''HH'')');
>
> It works as expected. But the problem I have is if the job gets broken
> or the db is down the next time it executes it will schedule itself 12
> hours in the future- if it happens to run at 2:10am then the next run
> would be 2:00pm. Is there a way with 1 job to force it to only run at
> 12:30am and 12:30pm regardless of the time of current execution?
>
> Thanks
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 02 2005 - 16:35:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US