Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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:

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

  DBMS_OUTPUT.PUT_LINE('Running fine!');

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;

 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

(6 rows)


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 software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter