The requirement is to create a job in Oracle to run daily at 3:00 AM and execute a stored procedure to perform a certain task. For example, the stored procedure name is PROC_DAILY_UPDATES.
To achieve this, the following example uses Oracle's DBMS_SCHEDULER.CREATE_JOB procedure:
1. Create a Job in Oracle using DBMS_SCHEDULER.CREATE_JOB
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'JOB_DAILY_UPDATES', job_type => 'STORED_PROCEDURE', job_action => 'YOURSCHEMA.PROC_DAILY_UPDATES', start_date => '28-SEP-19 03.00.00 AM America/New_York', repeat_interval => 'FREQ=DAILY;INTERVAL=1', end_date => NULL, auto_drop => FALSE, job_class => 'SYS.DEFAULT_JOB_CLASS', comments => 'extract data'); END; /
After creating the above job, enable it:
2. Enable the Job using DBMS_SCHEDULER.ENABLE
BEGIN
DBMS_SCHEDULER.enable('JOB_DAILY_UPDATES');
END;
/To confirm the job created successfully use the following query:
Select * from user_scheduler_jobs where job_name = 'JOB_DAILY_UPDATES';
From the above query you will get the information like last start date, next run date, and job enables status, etc.
To get the job execution history use the following query:
Select * from user_scheduler_job_run_details where job_name = 'JOB_DAILY_UPDATES';
You can also notice that in the above DBMS_SCHEDULER.CREATE_JOB procedure example, I used the timezone America/New_York. You can get the timezone of a specific country using the following query:
SELECT DISTINCT tzname, TZ_OFFSET (tzname) FROM V$TIMEZONE_NAMES ORDER BY tzname;
Output
America/Mexico_City -05:00 America/Miquelon -02:00 America/Moncton -03:00 America/Monterrey -05:00 America/Montevideo -03:00 America/Montreal -04:00 America/Montserrat -04:00 America/Nassau -04:00 America/New_York -04:00 ...
