EnterpriseDB: Schedule DB Jobs
Oracle Tips by
schedule a job by setting the repeat interval of an anonymous block.
Figure 6.14 displays the Schedule Job page.
6.14: Schedule Procedural Jobs
the database where you wish to run the job by selecting it in the
database drop down.
frequency, you can choose Interval, Daily, Weekly and Monthly.
For a monthly schedule, you would select a numeric day (1-31) and
time. For a weekly schedule, you select the day (sun-sat) and
time. For a daily schedule, you select the time to start.
For this example, I am choosing interval.
interval accepts an interval of time to wait between runs. You
can enter hours, minutes and seconds. I am allowing 1 minute
choose to write an anonymous block using either PL/pgSQL or SPL.
In my example above, I am choosing SPL.
the Language drop down is a check box labeled, Execute once before
running. If you select this checkbox, the job will run when you
choose Save. This allows you to test your code while you're
sitting there rather than waiting for a phone call at two in the
morning when it fails in production.
bottom part of the screen is the actual code that will run. It
is broken into a variable declaration section and a code section.
For me, the easiest way to write code for jobs is to write the code
and test it first. For example, I am using the following code as
v_text schedule_test.msg_text%TYPE := 'Howdy!';
v_date_and_time DATE := current_timestamp;
INSERT INTO schedule_test
VALUES (v_text, v_date_and_time);
run the above code in SQL Interactive until it completes successfully.
When the code will run in the code editor, I cut the declaration
section and put it in the job declaration section. I cut the
code and paste that into the code area on the schedule page. My
end result is displayed above in Figure 6.14.
have all of the information keyed in, press the Submit Job button.
If the job is successfully scheduled, the field on the screen will
blank out and you should see a message at the top of the screen saying
"Job Successfully Scheduled".
waiting a few minutes, I can launch PSQL and view the data in the
edb=# select * from schedule_test;
msg_text | date_and_time
Howdy! | 01-JAN-07 09:34:19.484
Howdy! | 01-JAN-07 09:37:09.39
Howdy! | 01-JAN-07 09:37:09.671
Howdy! | 01-JAN-07 09:38:09.781
Howdy! | 01-JAN-07 09:39:09.75
Howdy! | 01-JAN-07 09:40:09.562
Oracle with the DBMS_JOB and DBMS_SCHEDULER packages, there is no
interface to the job scheduler from SPL at this time. I fully
expect to see that in time, though.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.