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: Available Arguments for Merging Collections describes the functions available in the APEX_PLSQL_JOB package.

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: APEX_PLSQL_JOBS View Columns describes the columns available in APEX_PLSQL_JOBS view.

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.