DBMS Job not running (time change)

[David.E.M....@gmail.com]

I am working maintenance on a software system and I have found the
cause of a bug but I am not sure of the best way to fix it. Problem
is: we have 1 and only 1 dbms job scheduled to run every 60 seconds.
When we change the time back (for testing or fall timechange) the job
stops until the time catches up.

Any ideas on a fix?

Current fix options are:
A. Check for a bad next_date in the job when running other stored
procedures that are triggered by c++ code every 10 seconds.
B. Ditch the job queue and run this like other stored procdures in the
10 second c++ triggers. (what happens if this procedure hasn't finished
in 60 seconds and I try to call it again???)
C. Find some way to catch a time change notification in oracle (???)
and fix it then.
D. Make the BDMS job queue only consider seconds when running a job.
(eg. set it up to run every 59th second no matter what day or hour it
is. ???)
E. Something else that I haven't thought of.

Thanks much!

[DA Morgan]

I vote D as it corresponds with the business rule.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)

[David.E.M....@gmail.com]

You say option D but how do I do this? The parameters in the dbms job
submit function are of type date. Is there a way to trick it to only
look at the seconds and not the hours and minutes?

Anyway, I implemented option A, here it is:

-- Check the get_run_summary job in the job queue for a bad
-- DBMS_JOB.NEXT_DATE which
-- could be the result of a time change. get_run_summary is scheduled
-- to run every 60 seconds so a bad date would be outside this range.
-- If the date is bad fix it.

-- Retrieve the summarization job from the Oracle job queue schedule.
SELECT * INTO l_job_rec
FROM all_jobs WHERE what = 'get_run_summary;';

-- If this_date is NOT NULL, it indicates the job is currently running.
-- If the job is running assume the date is good.
IF l_job_rec.this_date IS NULL THEN

-- Get the next.date and check to see if it is > ( now + one minute )
-- If the next.date is > ( now + 1 minute ) then set it to now + 1 min
-- Ignore the case where the next_date is < sysdate because this job
-- is already backlogged with a high priority.

IF l_job_rec.next_date > (sysdate+1/1440) THEN
-- Reschedule the summarization to run 1 minute from now.
DBMS_JOB.NEXT_DATE
(l_job_rec.job, sysdate+1/1440);
COMMIT;
END IF;
END IF;

[DA Morgan]

1/8640th of a day.

[David.E.M....@gmail.com]

Ummm, elaborate please...

[DA Morgan]

David.E.M....@gmail.com wrote:

>>1/8640th of a day.

> Ummm, elaborate please...

Here's the DBMS_JOB syntax

dbms_job.isubmit (
job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE);

next_date is TRUNC(SYSDATE)
interval is 'TRUNC(SYSDATE)+(1/8640)'

[David.E.M....@gmail.com]

I finally got around to trying this. Apparently oracle doesn't like
the interval...

SQL> variable v_JobNo number;
SQL> exec dbms_job.submit(:v_JobNo, 'test;', TRUNC(SYSDATE),
'TRUNC(SYSDATE)+(1/8640)');
BEGIN dbms_job.submit(:v_JobNo, 'test;', TRUNC(SYSDATE),
'TRUNC(SYSDATE)+(1/8640)'); END;

*
ERROR at line 1:
ORA-23420: interval must evaluate to a time in the future
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at line 1

[David.E.M....@gmail.com]

"ORA-23420: interval must evaluate to a time in the future"

I'm glad the flux capacitor wasn't running as a DBMS job.