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

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.

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;