UPDATE_JOB_STATUS Procedure

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

Syntax

APEX_PLSQL_JOB.UPDATE_JOB_STATUS (
    p_job IN NUMBER,
    p_status IN VARCHAR2);

Parameters

Table 17-5 describes the parameters available in the UPDATE_JOB_STATUS procedure.

Table 17-5 UPDATE_JOB_STATUS Parameters

Parameter Description

p_job

The job ID for the job you want to update the status of.

p_status

The string of up to 100 characters to be used as the current status of the job.

Example

The following example shows how to use the UPDATE_JOB_STATUS procedure. 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 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 update happens 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.

BEGIN
    FOR i IN 1 .. 100 LOOP
        INSERT INTO emp(a,b) VALUES (:APP_JOB,i);
        IF MOD(i,10) = 0 THEN
            APEX_PLSQL_JOB.UPDATE_JOB_STATUS(
                P_JOB => :APP_JOB,
                P_STATUS => i || ' rows inserted');
        END IF;
        APEX_UTIL.PAUSE(2);
    END LOOP;
END;