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.
sql-statement : A valid sql of pl/sql statement
when : date/time the job has to be executed
status : text
apex_plsql_job.submit_process ('begin name_procedure;end;',sysdate,'Start');
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.
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.
In Apex (process behind a button)
l_sql := 'begin
l_job :=APEX_PLSQL_JOB.SUBMIT_PROCESS (l_sql,sysdate,'Start');
p_param1 IN varchar2,
p_param2 IN varchar2,
p_jobid IN NUMBER) IS
APEX_PLSQL_JOB.UPDATE_JOB_STATUS(P_JOB => p_jobid
, P_STATUS => 'Other procedure 1 finished');
-- Other procedures an job updates
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;