First of all, let me make a correction to Mike's code: You
want to base the interval on TRUNC(SYSDATE,'MI') because
when the next date to run the job is calculated, the current
time will not be exactly the time you expect. For instance,
Mike's interval is meant to be every minute starting March
10, 2008 at 10:35:41. It will be some time AFTER that time
when the next time to run is calculated - say 10:36, so the
next date will be 10:37. On the next run, the time might be
10:37:15 when the next time is calculated, so it will be
computed for 10:38:15. And so on. By using
interval=>'trunc(sysdate,''MI'')+1/(24*60)', it will base
the calculation on a zero based particular minute, and will
not be affected by variations in the exact second that the
next date was calculated.
Which brings me to my second point: You cannot time the
start of a job to the second. The job running mechanism is
not that precise. This is true of the Windows and Unix job
scheduling mechanisms too. It is basically running a
continuous loop, checking the current date and time against
the date and time that each scheduled job is supposed to be
run. When it finds a job which has a next run date/time
less than the current date/time, it starts it, and the first
thing it does is calculate the new next date/time. Notice
that it may not have started the job precisely when it was
scheduled; it started it when it got to it in the loop, and
the loop takes time to run. The loop is pretty fast, and is
usually fast enough to start jobs sometime during the minute
for which they were scheduled, but you can't depend on the
second.
-----Original Message-----
Sent: Friday, October 03, 2008 6:46 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L
Hi Akhilesh,
Here is an example:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'your_procedure;'
,next_date => to_date('03/10/2008
10:35:41','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate + 1 / (24 * 60)'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' ||
to_char(x));
END;
/
commit;
Assuming your running your database on windows, make sure
the
OracleJobScheduler service is running.
Mike.
----- Original Message -----
To: "Multiple recipients of list ODTUG-SQLPLUS-L"
<ODTUG-SQLPLUS-L fatcity.com>
Sent: Friday, October 03, 2008 10:45 AM
>
> Hi,
>
> This is Akhilesh, I am not able to execute my
scripts/procedure at exact
> time I mean on specific time in seconds like (HH:MI:SS)
05:59:59 AM. I am
> able to execute it at 05:59 I mean not in seconds, thru
Windows Task
> Scheduler.
>
> Can somebody is having a example of scheduler thru
DBMS_JOB PACKAGE ?
> If yes please help and send me the procedure
step-by-step.
>
> Thanking You
> Akhilesh
--
For more information on this topic or to become a member,
visit our Web site at http://www.ODTUG.com
Join ODTUG for The PL/SQL and The APEX Expert Gathering of
the Year! OPP2008 and APEXposed! 2008 will be held on
October 29-30, 2008 at the Wyndham O'Hare, Chicago. Visit
www.odtugopp.com or www.odtugapextraining.com for more
details.
--
Author: John Flack
INET: JohnFsmdi.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
------------------------------------------------------------
---------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: ListGurufatcity.com (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB
ODTUG-SQLPLUS-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).
|