One of the solutions is the use of the package apex_plsql_job.
This package consists of several procedures/functions
submit_process (submit a job)
update_job_status (update status)
purge_process (clean up submitted jobs)
The submitted jobs stay in the view apex_plsql_jobs.
job Unique job number
created date and time job was created
completed date and time job was finished
status status field updated by procedure update_job_status
system_status status field updated by oracle (Pending, In Progress, Completed)
Submit a job
If you want to submit a job you use the function submit_process which returns the jobid.
apex_plsql_job.submit_process (sql-statement,when,status)
sql-statement : A valid sql of pl/sql statement
when : date/time the job has to be executed
status : text
Example
apex_plsql_job.submit_process ('begin name_procedure;end;',sysdate,'Start');
Update status
By quering the view apex_plsql_jobs the user can get info about the status of a job (running, pending or completed). However, somtetimes it can be handy to have more detailed information about the status. With the procedure apex_plsql_job.update_job_status the status of the job can be updated by the 'user'. Of course detailed info can also be placed in a seperate log table.
apex_plsql_job.update_job_status(jobid, status)
Example
apex_plsql_job.update_job_status(212,'Still busy');
To update the status, you need an job-id, but you only get a job-id after you submitted a job. So, how do you update the status from within the job? You use a special construction with :APP_JOB.
Example
In Apex (process behind a button)
declare
l_job number;
l_sql varchar2(200);
begin
l_sql := 'begin
name_procedure(p_param1,p_param2,:APP_JOB);
end;';
l_job :=APEX_PLSQL_JOB.SUBMIT_PROCESS (l_sql,sysdate,'Start');
end;
Stored Procedure
PROCEDURE name_procedure(
p_param1 IN varchar2,
p_param2 IN varchar2,
p_jobid IN NUMBER) IS
BEGIN
other_procedure1(p_param1,p_param2);
APEX_PLSQL_JOB.UPDATE_JOB_STATUS(P_JOB => p_jobid
, P_STATUS => 'Other procedure 1 finished');
--
-- Other procedures an job updates
--
END;
Additional info
Check the status of job
Of course the user wants to know when the job is finished. There are several options.
1. A seperate window where user can query the view apex_plsql_jobs (with jobid). The user has to check manually (you could enabel automatic refresh in the window of course)
2. Register which user submited the job and send this user an email when job is finished
3. Use websockets, as soon as job is finished the user (if still in application) is notified. Although I think websockets is more for realtime solutions.
4. Or a combination.
Check if jobs are enabled on this database
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;
Link
http://docs.oracle.com/cd/E11882_01/appdev.112/e12510/apex_plsql_job.htm
