Using the Oracle scheduler in APEX
We often give APEX users the opportunity to have displayed reports emailed to them as PDF attachments. It may take minutes of processing to produce such an email, and it would be completely unacceptable to expect a web user to wait for the processing to complete. The solution is to use the Oracle scheduler, available in versions 10g and above. The call from APEX to schedule the report then takes at most a few seconds.
The Oracle scheduler is designed to run pre-defined scheduler jobs, which are PL/SQL procedures or anonymous blocks of PL/SQL. However all our report production is done by procedures that are defined as part of PL/SQL packages, and we are constantly writing new ones. We didn’t want the overhead of defining and maintaining a separate job for each procedure. Instead, we wrote a generalized interface to the scheduler that allows the creation and scheduling of a new job on demand. From APEX, an emailed report can then be scheduled for immediate or future production from a single PL/SQL call.
For each procedure that is to be called by the scheduler, we create a wrapper procedure with the same parameters. The wrapper procedure is the one that is called from APEX. We also declare a type arglist_t as a table of records to hold the details of each parameter.
In the wrapper procedure we assign the name, type, default value, and actual value of each parameter to elements of a variable of type arglist_t, then call the single procedure ScheduleSPJob.
A specific example – scheduling a call to a procedure ResendEmail(p_email_id IN NUMBER, p_recipients IN VARCHAR2).
Ensuring scheduler jobs are enabled
Scheduler jobs with arguments must be created as disabled, then be explicitly enabled after the arguments have been set. Our PL/SQL scheduling procedure ScheduleSPJob tries to enable each job after it is created. But if many jobs are scheduled in a short time – eg from a loop that emails several users – it is likely that some of them will not have been been available for enabling at the time they were initially created. They must then be enabled by a separate process call. We use an On Demand Application Process that calls a PL/SQL procedure to do this: APEXTRAS_SCHEDULER.EnableJobsByCreator(‘APEX_PUBLIC_USER’);. In our system, we have found it is sufficient to call this On Demand process once from an Apex Page Process immediately after the Page Process that creates the scheduled jobs.
The current state of all scheduled jobs can be seen in the view user_scheduler_jobs. Jobs created from Apex have JOB_CREATOR set to ‘APEX_PUBLIC_USER’.