Creating scheduled jobs in ORACLE

Share

When you need to run an ORACLE DB task every day the best choice is to use the Scheduler.

Scheduled Jobs can be used to run PL/SQL Blocks, Stored Procedures or even scripts.

To setup the job, we need to fill in some mandatory fields:
OWNER: this is the database used
JOB_NAME: this is a unique identifier for the job
ENABLED: has to be set to TRUE if we want this job to run
JOB_TYPE: this can be STORE_PROCEDURE (I choose stored procedure for the example)
JOB_ACTION: here is the name of the stored procedure
SCHEDULE_TYPE: CALENDAR
START_DATE and END_DATE have to define the minimum and maximum day when this job will run
REPEAT_INTERVAL: FREQ=DAILY;BYTIME=140000,020000; this setting will make the job run every day at 2am and 2pm.
 
ORRaF
 
A tool that can be very helpful in creating this kind of jobs is ORACLE SQL Developer, where we have the Scheduler section.
 
02-Scheduler-Node
 
Scheduled jobs also store a history, were we can see if a job was executed with success, or if it failed (in this case the log gives us the error code)
 
Oracle-Job-Scheduler-Program-Output

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close