EnterpriseDB: Schedule DB Jobs
Oracle Tips by Burleson

You schedule a job by setting the repeat interval of an anonymous block. Figure 6.14 displays the Schedule Job page.

Figure 6.14: Schedule Procedural Jobs

Choose the database where you wish to run the job by selecting it in the database drop down.

For job 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.

The interval accepts an interval of time to wait between runs. You can enter hours, minutes and seconds. I am allowing 1 minute between runs.

You can choose to write an anonymous block using either PL/pgSQL or SPL. In my example above, I am choosing SPL.

Beneath 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.

The 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 my job:

DECLARE
v_text schedule_test.msg_text%TYPE := 'Howdy!';
v_date_and_time DATE := current_timestamp;
BEGIN
INSERT INTO schedule_test
(msg_text, date_and_time)
VALUES (v_text, v_date_and_time);

DBMS_OUTPUT.PUT_LINE('Running fine!');
END;

I will 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.

Once you 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".

After waiting a few minutes, I can launch PSQL and view the data in the schedule_test table:

edb=# select * from schedule_test;

edb=#

Unlike 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.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

EnterpriseDB: Schedule DB Jobs Oracle Tips by Burleson

EnterpriseDB: Schedule DB Jobs
Oracle Tips by Burleson