This discussion is archived
2 Replies Latest reply: Jan 21, 2013 7:10 AM by MartinF RSS

Updating Job Status using APEX_PLSQL_JOB.UPDATE_JOB_STATUS in Procedure

MartinF Explorer
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points