RE: DBMS job interval question -- SOLVED well, sort of

From: John Dailey <John.Dailey_at_concept-solutions.com>
Date: Tue, 5 Dec 2000 17:04:20 -0500
Message-Id: <10701.123726@fatcity.com>

Thanks for all the suggestions... unfortunately I'm going to have to go with the 4 job # solution. I tried the solution below with dbms_job.interval but I got the following error:

PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only

but it was definitely a good suggestion and worth a shot. Thanks again for all who replied!

John Dailey, MCSE/MCP+I
Oracle DBA & All Around Good Guy
Consultant
Concept Solutions, LLC
Phone: 703-855-8832

Concept Solutions, LLC
*Your Business Intelligence Partner*
www.concept-solutions.com

-----Original Message-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jeffery Stevenson
Sent: Tuesday, December 05, 2000 1:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS job interval question

I haven't tried putting this into an interval section in a DBMS_JOB, but if you want to test it out...

trunc(sysdate) + (decode(to_char(sysdate,'HH24'),

                    greatest(to_char(sysdate,'HH24'),17),1,
                    greatest(to_char(sysdate,'HH24'),11),18/24,
                    greatest(to_char(sysdate,'HH24'),5),12/24,
                    6/24))

As long as the jobs don't take over 6 hours to run, this should work to have the job run at the appropriate next interval. HTH

Jeffery Stevenson
Chief Databeast Tamer
Medical Present Value, Inc.
Austin, TX

-----Original Message-----

Sent: Tuesday, December 05, 2000 10:51 AM To: Multiple recipients of list ORACLE-L

Greetings all. I want to schedule a job to run at midnight, 6am, noon, and 6pm each day. How would I write the job interval to schedule this, or do I have to enter the job 4 different times (i.e. 4 different job numbers) with the following intervals:

TRUNC(SYSDATE+1)
TRUNC(SYSDATE+1)+6/24
TRUNC(SYSDATE+1)+12/24
TRUNC(SYSDATE+1)+18/24

How can I combine these into one? Or is this possible?

Thanks in advance for any and all help. Anybody done this before?

John Dailey, MCSE/MCP+I
Oracle DBA & All Around Good Guy
Consultant
Concept Solutions, LLC
Phone: 703-855-8832

Concept Solutions, LLC
*Your Business Intelligence Partner*
www.concept-solutions.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: John Dailey
INET: John.Dailey_at_concept-solutions.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jeffery Stevenson
INET: jeff_at_mpv.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message