APEXtras

a development team dedicated to Oracle APEX

Using the Oracle scheduler in APEX

with 2 comments

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.

More details:

Calling interface

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.

The generalized form of the wrapper procedure.

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’.

Written by Roger

May 14th, 2009 at 3:59 pm

Posted in Uncategorized

Tagged with , , ,

2 Responses to 'Using the Oracle scheduler in APEX'

Subscribe to comments with RSS or TrackBack to 'Using the Oracle scheduler in APEX'.

  1. Nice Post! What types (PL/PDF, BI-Publisher…) of reports are you running through the scheduler?

    Regards,
    Todd

    Todd

    14 May 09 at 23:32

  2. We’re currently only scheduling PL/PDF reports.

    I haven’t yet had time to get BI-Publisher or XSL-FO reports working – I know there’s an issue with setting up the environment so that the call from the scheduler can access the correct APEX page.

    Roger

    15 May 09 at 11:14

Leave a Reply