Use Job ID as a procedure parameter

Question

I am using DBMS_JOB. Is it possible to pass jobId (which is an OUT parameter from the submit method) as a parameter of the calling procedure?

This is what I am trying:

jobno                 NUMBER;

sql_string:= 'BEGIN BPM_API_BATCH.' || l_procedure_name || '(:jobno, sysdate);  END;';

DBMS_JOB.SUBMIT (jobno,
                 sql_string,
                 sysdate,
                 null);

score 4 · Accepted Answer · answered Oct 28 '13 at 14:36

4

Do you really need to pass the job number in as an argument?

Within the job, you can call SYS_CONTEXT( 'USERENV', 'BG_JOB_ID' ) to get the job_id without needing to pass it in as a parameter (that will return NULL if the procedure is not called in a job.

answered Oct 28 '13 at 14:36

Justin Cave

227,342
24
367
384

Yes, that is what I needed. I didn't know it could be fetched this way. Tx – dinafication Oct 28 '13 at 14:42

score 0 · Answer 2 · answered Oct 28 '13 at 14:48

A DBMS_JOB.WHAT procedure can be used to change the PL/SQL to run:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_job.htm#i1000977

CREATE TABLE testt(
  val varchar2(100)
);

DECLARE
  jobno   NUMBER;
BEGIN
   DBMS_JOB.SUBMIT(
      job =>      jobno,
      what =>     'BEGIN NULL; END;',
      NEXT_DATE => sysdate
   );
   DBMS_JOB.WHAT(
      job  => jobno,
      what => 'BEGIN INSERT INTO testt VALUES(''jobno = ' || jobno || ''' ); commit; end;'
    );
    commit;
END;
/



SELECT * FROM testt;

VAL    
-------------
jobno = 26

Use Job ID as a procedure parameter

2 Answers2