Tuesday, 15 October 2013

Using jobs in Apex

Sometimes you just want to the user to be able to start a procedure/package as a job, so the user doesn't have to wait till the job is finished.

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

1 comment: