So I’m working with apex to get some things done and I think it’s a nice tool, it already has everything in it so you can create quick reports and other fancy web tools so I designed a report on PL/SQL and I had the need to have it run daily, after finding out how I thought you might find useful this snippets of code.
We use the dbms_scheduler.create_job() API to easily add jobs to APEX’s scheduler:
BEGIN dbms_scheduler.create_job( job_name => 'JOB_NAME', job_type => 'STORED_PROCEDURE', job_action => 'procedureName', start_date => '04-JUL-12 07.00.00.000000 AM -05:00', repeat_interval => 'FREQ=DAILY', enabled => TRUE, comments => 'My super report, dont delete.'); END;
To check for existing jobs on the scheduler we can query the user_scheduler_jobs table;
SELECT * FROM user_scheduler_jobs;
And lastly and just as useful, to delete a job from the scheduler we use the dbms_scheduler.drop_job() API call:
BEGIN dbms_scheduler.drop_job( job_name => 'JOB_NAME'); END;
Hope you guys on the internetz find this useful.