Run PL/SQL in the background and display a progress bar

To run PL/SQL procedures within an Application Express application, is daily developers work. All business logic within APEX is done with PL/SQL. Sometimes, PL/SQL procedures might take more time to complete - and since we are talking about web applications, that means that the end users will have to wait for their browsers to respond. This is OK up to a few seconds, but when execution times reach minutes, this quickly becomes inacceptable.

In this how to document, you will learn, how to run PL/SQL code in the background using the database package DBMS_SCHEDULER, how to query the jobs' status and how to display a nice progress bar in your application which informs end users about the status of their background operation.

Send PL/SQL code to the background

Background PL/SQL code is, from the database's point of view, a scheduler job. Database administrators use these frequently - for application developers, it's not that common. As an example, we will use the following PL/SQL procedure (replace {my-workspace} with your workspace name).

create or replace procedure do_longrun as
begin
  apex_util.set_workspace('{my workspace}');
  -- APEX 5.0 and earlier: 
  -- apex_util.set_security_group_id(apex_util.find_security_group_id('{my workspace}'));
  for i in 1..30 loop
    apex_util.pause(10);
  end loop;
end;

If we ran that PL/SQL procedure as a "normal" page process of type PL/SQL, the browser would indeed be stalled for 5 minutes. That is not only a very long time to wait, in some environments, the front-end webservers even terminate connections after some time. So, for the end user, the APEX page might even error out.

So we run that code as a job, using DBMS_SCHEDULER. On your Application Express instance, you will need the CREATE JOB privilege for that. On the public Oracle evaluation instance apex.oracle.com, the privilege is already granted.

declare
  l_jobname  varchar2(255);
begin
  -- first generate a unique name for the job
  l_jobname := dbms_scheduler.generate_job_name('APEXCOMMUNITY_');

  -- then fire it
  dbms_scheduler.create_job(
    job_name            => l_jobname,
    job_type            => 'stored_procedure',
    job_action          => 'DO_LONGRUN',
    enabled             => true
  );
end;

Create an Application Express application and a page within that app. Then create a page process of type PL/SQL using the above code. Create a button on your page (Run Job) and have the page process being executed upon button click. When you run the page and click the button, the result should look as follows.

The PL/SQL code has been fired as a background job

Using the data dictionary view USER_SCHEDULER_JOBS, you can verify whether your job is really running. The view contains much more columns providing all kind of information about your job - have a look. There is also the view USER_SCHEDULER_JOB_RUN_DETAILS in which you can check results or issues of already finished jobs. To provide that information to the end user, you might simply use Application Express reports.

SQL> select job_name, state from user_scheduler_jobs;

JOB_NAME                       STATE
------------------------------ ---------------
APEXCOMMUNITY_687              RUNNING
APEXCOMMUNITY_688              RUNNING
APEXCOMMUNITY_689              RUNNING

But that is not really satisfying - we can see that a job is running, but not how far it already got. In our case, we could simply check the expired time (we know that the job runs 5 minutes), but in practice, things are not so easy. Jobs might have to work on a number of table rows - and we cannot simply map that to expired minutes or seconds. We would like to have our job frequently reporting on its status - and the following sections will explain how this can work.

Excursion: Desupport of the APEX_PLSQL_JOB package

As already announced with the APEX 5.0 release, APEX_PLSQL_JOB (which relied on the old DBMS_JOB package) has been removed. APEX_PLSQL_JOB was used to run PL/SQL logic in the background in the very early APEX releases. Since we have DBMS_SCHEDULER since Oracle10g (that means, since 2004), that package has been removed with APEX 5.1. Applications using APEX_PLSQL_JOB should be migrated to use DBMS_SCHEDULER, preferrable before the instance is upgraded to 5.1.

Here is an example for some code to run PL/SQL logic in background with APEX_PLSQL_JOB:

DECLARE
    l_sql  VARCHAR2(4000);
    l_job  NUMBER;
BEGIN
    l_sql := 'begin plsql_to_run_as_job( :P1_ITEM ); end;';
    l_job := APEX_PLSQL_JOB.SUBMIT_PROCESS(
        p_sql    => l_sql,
        p_status => 'Background process submitted' );

    :P1_JOB_ID := l_job;
END;

The following code does the same; but uses DBMS_SCHEDULER.

DECLARE
    l_sql varchar2(4000);
    l_job varchar2(4000);
BEGIN
    l_sql := 'begin plsql_to_run_as_job( ' || 
              dbms_assert.enquote_literal( :P1_ITEM ) || 
             ' ); end';
    l_job := dbms_scheduler.generate_job_name ( 
        prefix => 'MY_APEX_JOB' );

    dbms_scheduler.create_job(
        job_name =>   l_job,
        job_type =>   'PLSQL_BLOCK',
        job_action => l_sql,
        comments =>   'Background process submitted',
        enabled =>    true );
END;

Applications or PL/SQL code using APEX_PLSQL_JOB should therefore be migrated to use DBMS_SCHEDULER. That should be done even when still on APEX 5.0 or 4.2. Having this done, applications will run without issues after the upgrade to 5.1.

Report job status to Database Administrators and End Users

Background jobs executed with DBMS_SCHEDULER run within their own database session, so an APEX component cannot look into global PL/SQL package variables. The job could maintain status within their own tables, but that would require the job to also commit after each status update. Also, we want to use the most generic infrastructure as possible: not only APEX end users are interested in the jobs' status, that information is important for database administrators as well.

The Oracle database contains a built-in facility to report on the status of a long running job: The V$SESSION_LONGOPS data dictionary view. And Oracle itself uses that view: When executing a CREATE TABLE AS SELECT statement to copy a very large table, you can monitor the progress using this view. Every database user can access V$SESSION_LONGOPS, so we can use it within our APEX application. Also, the database administrator knows about that view, uses it frequently and so gets informed about long running operations of the APEX application.

To report status information to V$SESSION_LONGOPS, we have to use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure. Its interface is a bit cumbersome, to make it more easy, we first create ourselves a tiny "Wrapper-Package" ...

create or replace package pkg_session_longops is
  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2);
  procedure do_update (p_opname in varchar2, p_status in number);
end pkg_session_longops;
/
sho err

create or replace package body pkg_session_longops is
  type t_array is table of number index by varchar2(255);
  g_arr_rindex t_array;
  g_arr_slno   t_array;
  g_arr_total  t_array;

  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2) is
    l_rindex binary_integer := dbms_application_info.set_session_longops_nohint;
    l_slno   binary_integer;
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => 0,
      totalwork    => p_target,
      target_desc  => 'no target',
      units        => p_units
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
    g_arr_total(p_opname) := p_target;
  end do_init;
      
  procedure do_update (p_opname in varchar2, p_status in number) is
    l_rindex binary_integer := g_arr_rindex(p_opname);
    l_slno   binary_integer := g_arr_slno(p_opname);
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => p_status,
      totalwork    => g_arr_total(p_opname),
      target_desc  => 'no target',
      units        => null
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
  end do_update;
end pkg_session_longops;
/
sho err

Our new PKG_SESSION_LONGOPS package contains two procedures: DO_INIT creates a new entry in V$SESSION_LONGOPS for our job. DO_UPDATE reports a status update to that entry. V$SESSION_LONGOPS is not transactional, it's not really a table. All operations are done in memory and should therefore have no performance impact. So change the DO_LONGRUN procedure we have created at the beginning, to use the new PKG_SESSION_LONGOPS package as follows:

create or replace procedure do_longrun as
begin
  apex_util.set_workspace('{my workspace}');
  -- APEX 5.0 and earlier: 
  -- apex_util.set_security_group_id(apex_util.find_security_group_id('{my workspace}'));
  pkg_session_longops.do_init('DO_LONGRUN', 300, 'seconds');
  for i in 1..30 loop
    apex_util.pause(10);
    pkg_session_longops.do_update('DO_LONGRUN', (i * 10));
  end loop;
end;

Now run your page and fire another job execution. Using SQL Plus or SQL Workshop you can monitor job status.

SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        60        300 seconds

1 row selected.

SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        70        300 seconds

1 row selected.

Display job status as a progress bar

Using the above SQL query, we can easily create an APEX report ...

select 
  opname,
  sofar, 
  totalwork,
  round((sofar / totalwork) * 100) as percent
from v$session_longops 
where opname = 'DO_LONGRUN'
and sofar < totalwork

... and for the PERCENT column (which returns a value between 0 and 100), the Percent Graph type is a perfect fit.

Change a report column type to "Percent Graph"

The result should look as in the following screenshot. You might even extend that to have the report automatically refresh using the Timer plug-in from the Application Express Plug-Ins page.

Report job status to the end user with a progress bar

Summary

In this how to document you have learned how to use the DBMS_SCHEDULER package to run PL/SQL procedures as a background job. That is particularly useful for PL/SQL operations which require more than a few seconds to complete. End users don't experience a stalled browser and application acceptance will increase.

Using the V$SESSION_LONGOPS view the job can frequently report on its current status; with APEX reports or charts we are then able to provide such status information to the end user.

back to blogs.oracle.com/apex