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.
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.
Table 17-2 describes the functions available in the
|Function or Procedure||Description|
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.
Call this procedure to update the status of the currently running job. This procedure is most effective when called from the submitted PL/SQL.
Use this function to determine how much time has elapsed since the job was submitted.
Call this function to determine whether the database is currently in a mode that supports submitting jobs to the
Call this procedure to clean up submitted jobs. Submitted jobs stay in the
You can view all jobs submitted to the
APEX_PLSQL_JOB package using the
For complete descriptions of all Application Programming Interface packages for Oracle Application Express, see Oracle Application Express API Reference.
"APEX_PLSQL_JOB" in Oracle Application Express API Reference
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.
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
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
APP_JOB represents the job number which is assigned to this process as it is submitted to
APEX_PLSQL_JOB. By specifying this reserved item inside your process code, it is 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 are not 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 updates happen regardless of whether the job succeeds or fails. This last point is important for two reasons:
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.
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.