Running Background PL/SQL

You can use the APEX_PLSQL_JOB package to run PL/SQL code in the background of your application. This is an effective approach for managing long running operations that do not need to complete for a user to continue working with your application.

Topics in this section include:

Understanding the APEX_PLSQL_JOB Package

APEX_PLSQL_JOB is a wrapper package around DBMS_JOB functionality offered in the Oracle database. Note that the APEX_PLSQL_JOB package only exposes that functionality which is necessary to run PL/SQL in the background. The following is a description of the APEX_PLSQL_JOB package:

SQL> DESC APEX_PLSQL_JOB
FUNCTION JOBS_ARE_ENABLED RETURNS BOOLEAN
PROCEDURE PURGE_PROCESS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
FUNCTION SUBMIT_PROCESS RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SQL                          VARCHAR2                IN
 P_WHEN                         VARCHAR2                IN     DEFAULT
 P_STATUS                       VARCHAR2                IN     DEFAULT
FUNCTION TIME_ELAPSED RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
PROCEDURE UPDATE_JOB_STATUS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
 P_STATUS                       VARCHAR2                IN                         

Table 15-1 describes the functions available in the APEX_PLSQL_JOB package.

Table 15-3 APEX_PLSQL_JOB Package: Available Functions

Function or Procedure Description

SUBMIT_PROCESS

Use this procedure to submit background PL/SQL. This procedure returns a unique job number. Because you can use this job number as a reference point for other procedures and functions in this package, it may be useful to store it in your own schema.

UPDATE_JOB_STATUS

Call this procedure to update the status of the currently running job. This procedure is most effective when called from the submitted PL/SQL.

TIME_ELAPSED

Use this function to determine how much time has elapsed since the job was submitted.

JOBS_ARE_ENABLED

Call this function to determine whether the database is currently in a mode that supports submitting jobs to the APEX_PLSQL_JOB package.

PURGE_PROCESS

Call this procedure to clean up submitted jobs. Submitted jobs stay in the APEX_PLSQL_JOBS view until either Oracle Application Express cleans out those records, or you call PURGE_PROCESS to manually remove them.


You can view all jobs submitted to the APEX_PLSQL_JOB package using the APEX_PLSQL_JOBS view. The following is the description of APEX_PLSQL_JOBS view:

SQL> DESCRIBE APEX_PLSQL_JOBS
 Name                              Null?    Type
 --------------------------------- -------- ----------------------------
 ID                                         NUMBER
 JOB                                        NUMBER
 FLOW_ID                                    NUMBER
 OWNER                                      VARCHAR2(30)
 ENDUSER                                    VARCHAR2(30)
 CREATED                                    DATE
 MODIFIED                                   DATE
 STATUS                                     VARCHAR2(100)
 SYSTEM_STATUS                              VARCHAR2(4000)
 SYSTEM_MODIFIED                            DATE
 SECURITY_GROUP_ID                          NUMBER

Table 15-4 describes the columns available in APEX_PLSQL_JOBS view.

Table 15-4 APEX_PLSQL_JOBS View Columns

Name Description

ID

A unique identifier for each row.

JOB

The job number assigned to each submitted PL/SQL job. The APEX_PLSQL_JOB.SUBMIT_PROCESS function returns this value. This is also the value you pass into other procedures and functions in the APEX_PLSQL_JOB package.

FLOW_ID

The application from which this job was submitted.

OWNER

The database schema that owns the application. This identifies what schema will parse this code when DBMS_JOB runs it.

ENDUSER

The end user (that is, who logged into the application) that caused this process to be submitted.

CREATED

The date when the job was submitted.

MODIFIED

The date when the status was modified.

STATUS

The user-defined status for this job. Calling APEX_PLSQL_JOB.UPDATE_JOB_STATUS updates this column.

SYSTEM_STATUS

The system defined status for this job.

SYSTEM_MODIFIED

The date when the system status was modified.

SECURITY_GROUP_ID

The unique ID assigned to your workspace. Developers can only see jobs submitted from their own workspace.


See Also:

APEX_PLSQL_JOBS in Oracle Application Express API Reference

About System Status Updates

Submitted jobs can contain any of the following system status settings:

  • SUBMITTED indicates the job has been submitted, but has not yet started. The DBMS_JOB does not guarantee immediate starting of jobs.

  • IN PROGRESS indicates that the DBMS_JOB has started the process.

  • COMPLETED indicates the job has finished.

  • BROKEN (sqlcode) sqlerrm indicates there was a problem in your job that resulted in an error. The SQL code and SQL error message for the error should be included in the system status. Review this information to determine what went wrong.

Using a Process to Implement Background PL/SQL

The following example runs a PL/SQL job in the background for testing and explanation:

001  BEGIN
002    FOR i IN 1 .. 100 LOOP
003      INSERT INTO emp(a,b) VALUES (:APP_JOB,i);
004      IF MOD(i,10) = 0 THEN
005        APEX_PLSQL_JOB.UPDATE_JOB_STATUS(
006          P_JOB     => :APP_JOB,
007          P_STATUS  => i || 'rows inserted');
008      END IF;
009      APEX_UTIL.PAUSE(2);
010    END LOOP;
011  END;

In this example, note that:

  • Lines 002 to 010 run a loop that inserts 100 records into the emp table.

  • APP_JOB is referenced as a bind variable inside the VALUES clause of the INSERT, and specified as the P_JOB parameter value in the call to UPDATE_JOB_STATUS.

  • APP_JOB represents the job number which will be assigned to this process as it is submitted to APEX_PLSQL_JOB. By specifying this reserved item inside your process code, it will be replaced for you at execution time with the actual job number.

  • Note that this example calls to UPDATE_JOB_STATUS every ten records, INSIDE the block of code. Normally, Oracle transaction rules dictate updates made inside code blocks will not be seen until the entire transaction is committed. The APEX_PLSQL_JOB.UPDATE_JOB_STATUS procedure, however, has been implemented in such a way that the update will happen regardless of whether the job succeeds or fails. This last point is important for two reasons:

    1. Even if your status shows "100 rows inserted," it does not mean the entire operation was successful. If an error occurred at the time the block of code tried to commit, the user_status column of APEX_PLSQL_JOBS would not be affected because status updates are committed separately.

    2. Updates are performed autonomously. You can view the job status before the job has completed. This gives you the ability to display status text about ongoing operations in the background as they are happening.