2 Replies Latest reply: Jan 21, 2013 9:10 AM by MartinF RSS

    Updating Job Status using APEX_PLSQL_JOB.UPDATE_JOB_STATUS in Procedure

    MartinF
      Hi,

      I am having trouble getting my head around Job Status in APEX_PLSQL_JOB.

      I have a button on a page that submits a job using:
                l_sql := 'BEGIN validation_pkg.run_validations_job(NULL,:P7_APP_ID); END;';  
                l_job := APEX_PLSQL_JOB.SUBMIT_PROCESS(p_sql => l_sql,p_status => 'Validation Submitted');
      This creates a job running my Packaged Procedure and returns the JOB_ID which I store in a table so I can query its status later.

      The job then runs but I want it to update the status of the job as it progresses through the stages of processing it performs. I therefore want to call APEX_PLSQL_JOB.UPDATE_JOB_STATUS. This call requires two parameters, the JOB_ID and the new STATUS. The manual has the sample code:
      APEX_PLSQL_JOB.UPDATE_JOB_STATUS( P_JOB => :APP_JOB, P_STATUS => 'New Status');
      and states that :APP_JOB is a 'reserved item' that will be automatically 'replaced for you at execution time with the actual job number'.

      However, when I refer to :APP_JOB in my 'Create package body.....' code, like
      apex_plsql_job.update_job_status (P_JOB =>:APP_JOB, P_STATUS => 'Stage 1 Complete'); 
      it returns a compilation error of 'bad bind variable 'APP_JOB'.

      How do I refer to APP_JOB in my packaged procedure?

      Many thanks,
      Martin