Submitting multiple jobs in PLSQL using dbms_job.submit - Job not found in queue error

I want to kick off to separate (parallel) task in plsql using dbms_submit. (Note that I am not passing any interval data with my submit invocations).

In my example .. for some reason when I try to call run on the second job, I get a job not found in queue error. Any ideas on my the second job is not being added to the job queue?

set serveroutput on
DECLARE
  jobno number;
  jobno2 number;
BEGIN

  dbms_job.submit(jobno,'begin dbms_lock.sleep(10); dbms_output.put_line(''test1'');end;');
  commit;
  dbms_job.submit(jobno2,'begin dbms_lock.sleep(5); dbms_output.put_line(''test2'');end;');
  commit;

  dbms_output.put_line(jobno || ' ' || jobno2);

  dbms_job.run(jobno);
  dbms_job.run(jobno2);

END;
/

Error report:

ORA-23421: job number 154230 is not a job in the job queue ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_IJOB", line 770 ORA-06512: at "SYS.DBMS_JOB", line 267 ORA-06512: at line 14 23421. 00000 - "job number %s is not a job in the job queue" *Cause: There is no job visible to t开发者_运维问答he caller with the given job number. *Action: Choose the number of a job visible to the caller.

Output:

154229 154230

test1

If you specify NULL for a job interval, the behavior is that the job will not re-execute. Once it runs, it is deleted from the job queue. You didn't specify a NEXT_DATE either, so the jobs begin executing upon submit. When you execute this procedure, the two jobs are created and immediately begin executing. Before the first one can finish, you've run it again while it's still in the queue, which is why the first invocation succeeds. However, by the time the second job is run, it has finished the execution that the submit kicked off, and is therefore no longer in the queue. Here's a slight modification of your example to illustrate:

DECLARE
  jobno BINARY_INTEGER;
  jobno2 BINARY_INTEGER;
BEGIN

  dbms_job.submit(jobno,'begin dbms_lock.sleep(10); dbms_output.put_line(''test1'');end;', next_date=>SYSDATE+1/24);
  commit;
  dbms_job.submit(jobno2,'begin dbms_lock.sleep(5); dbms_output.put_line(''test2'');end;', next_date=>SYSDATE+1/24);
  commit;

  dbms_output.put_line(jobno || ' ' || jobno2);

  dbms_job.run(jobno);
  dbms_job.run(jobno2);
  dbms_job.run(jobno);
END;
/

I get the same error, but note the difference in output:

727 728
test1
test2

In this modified example, the jobs do not begin executing immediately, and the RUN procedure is able to execute the both, but only once. The second time I try to run the first job, it's been deleted by virtue of the first execution of it.

EDIT:

I don't know what version of Oracle you're using, but you might want to look into the much more robust DBMS_SCHEDULER package, as Oracle has replaced the DBMS_JOB package with it in version 10g. DBMS_JOB is provided for backward compatibility. Here's a good overview of the features of the scheduler.

set serveroutput on

DECLARE    
 jobno NUMBER;
 jobno2 NUMBER;
begin
dbms_job.submit(:jobno, 'begin dbms_lock.sleep(10); dbms_output.put_line(''test1'');end;', SYSDATE, 'SYSDATE + 1/86400', TRUE);
commit;
dbms_job.submit(:jobno2, 'begin dbms_lock.sleep(5); dbms_output.put_line(''test1'');end;', SYSDATE, 'SYSDATE + 1/86400', TRUE);
commit;
dbms_output.put_line(jobno || ' ' || jobno2);
END;

/