Skip Headers
Oracle® Application Express API Reference
Release 4.2

E35127-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to current chapter
Up
Go to next page
Next
PDF · Mobi · ePub

17 APEX_PLSQL_JOB

You can use 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:


About 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.

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

Table 17-1 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.


JOBS_ARE_ENABLED Function

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

Syntax

APEX_PLSQL_JOB.JOBS_ARE_ENABLED
RETURN BOOLEAN;

Parameters

None.

Example

The following example shows how to use the JOBS_ARE_ENABLED function. In the example, if the function returns TRUE the message 'Jobs are enabled on this database instance' is displayed, otherwise the message 'Jobs are not enabled on this database instance' is displayed.

BEGIN
    IF APEX_PLSQL_JOB.JOBS_ARE_ENABLED THEN
        HTP.P('Jobs are enabled on this database instance.');
    ELSE
        HTP.P('Jobs are not enabled on this database instance.');
    END IF;
END;

PURGE_PROCESS Procedure

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.

Syntax

APEX_PLSQL_JOB.PURGE_PROCESS (
    p_job IN NUMBER);

Parameters

Table 17-2 describes the parameters available in the PURGE_PROCESS procedure.

Table 17-2 PURGE_PROCESS Parameters

Parameter Description

p_job

The job number that identifies the submitted job you want to purge.


Example

The following example shows how to use the PURGE_PROCESS procedure to purge the submitted job identified by a job number of 161. You could also choose to purge all or some submitted jobs by referencing the APEX_PLSQL_JOBS view.

BEGIN
    APEX_PLSQL_JOB.PURGE_PROCESS(
        p_job => 161);
END;

SUBMIT_PROCESS Function

Use this function to submit background PL/SQL. This function 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.

Syntax

APEX_PLSQL_JOB.SUBMIT_PROCESS (
    p_sql IN VARCHAR2,
    p_when IN DATE DEFAULT SYSDATE,
    p_status IN VARCHAR2 DEFAULT 'PENDING')
RETURN NUMBER;

Parameters

Table 17-3 describes the parameters available in the SUBMIT_PROCESS function.

Table 17-3 SUBMIT_PROCESS Parameters

Parameter Description

p_sql

The process you want to run in your job. This can be any valid anonymous block, for example:

'BEGIN <your code> END;'
or
'DECLARE <your declaration> 
BEGIN <your code> END;'

p_when

When you want to run it. The default is SYSDATE which means the job runs as soon as possible. You can also set the job to run in the future, for example:

sysdate + 1 - The job runs in 1 days time.

sysdate + (1/24) - The job runs in 1 hours time.

sysdate + (10/24/60) - The job runs in 10 minutes time.

p_status

Plain text status information for this job.


Example

The following example shows how to use the SUBMIT_PROCESS function to submit a background process that starts as soon as possible.

DECLARE
    l_sql VARCHAR2(4000);
    l_job NUMBER;
BEGIN
    l_sql := 'BEGIN MY_PACKAGE.MY_PROCESS; END;';
    l_job := APEX_PLSQL_JOB.SUBMIT_PROCESS(
        p_sql => l_sql,
        p_status => 'Background process submitted');
    --store l_job for later reference
END;

TIME_ELAPSED Function

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

Syntax

APEX_PLSQL_JOB.TIME_ELAPSED(
    p_job IN NUMBER)
RETURN NUMBER;

Parameters

Table 17-4 describes the parameters available in the TIME_ELAPSED function.

Table 17-4 TIME_ELAPSED Parameters

Parameter Description

p_job

The job ID for the job for which you want to determine the time that has passed since it was submitted.


Example

The following example shows how to use the TIME_ELAPSED function to get the time elapsed for the submitted job identified by the job number 161.

DECLARE
    l_time NUMBER;
BEGIN
    l_time := APEX_PLSQL_JOB.TIME_ELAPSED(p_job => 161);
END;

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;