Scheduling jobs on Oracle Apex

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.

2 thoughts on “Scheduling jobs on Oracle Apex

  1. I need to truncate a table every 24 hours, and your code looks like I can tweak it to do that. But I have no idea how to implement that code?

    Using APEX, where would I “put” that code?

Leave a Reply

Your email address will not be published.