oracle 使用 dbms_job.submit 在 PLSQL 中提交多个作业 - 在队列中找不到作业错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4609843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Submitting multiple jobs in PLSQL using dbms_job.submit - Job not found in queue error
提问by vicsz
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).
我想开始使用 dbms_submit 在 plsql 中分离（并行）任务。（请注意，我没有通过提交调用传递任何间隔数据）。
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?
在我的示例中 .. 出于某种原因，当我尝试在第二个作业上调用 run 时，我在队列错误中找到了一个作业。关于我的第二份工作的任何想法没有被添加到工作队列中？
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 the caller with the given job number. *Action: Choose the number of a job visible to the caller.
ORA-23421：作业号 154230 不是作业队列中的作业 ORA-06512：在“SYS.DBMS_SYS_ERROR”，第 86 行 ORA-06512：在“SYS.DBMS_IJOB”，第 770 行 ORA-06512：在“SYS.DBMS_JOB” ", line 267 ORA-06512: at line 14 23421. 00000 - "job number %s is not a job in the job queue" *Cause: 调用者没有看到具有给定作业号的作业。*操作：选择呼叫者可见的工作号码。
Output:
输出：
154229 154230
154229 154230
test1
测试1
回答by DCookie
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:
如果您为作业间隔指定 NULL，则行为将不会重新执行作业。一旦运行，它就会从作业队列中删除。您也没有指定 NEXT_DATE，因此作业在提交时开始执行。执行此过程时，将创建两个作业并立即开始执行。在第一个完成之前，您已经在它仍在队列中时再次运行它，这就是第一次调用成功的原因。然而，当第二个作业运行时，它已经完成了提交开始的执行，因此不再在队列中。这是对您的示例的轻微修改以说明：
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.
在这个修改后的示例中，作业不会立即开始执行，RUN 过程能够执行这两者，但只能执行一次。我第二次尝试运行第一个作业时，由于第一次执行它已被删除。
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 overviewof the features of the scheduler.
我不知道您使用的是哪个版本的 Oracle，但您可能想要查看更强大的DBMS_SCHEDULER 包，因为 Oracle 已在 10g 版中用它替换了 DBMS_JOB 包。提供 DBMS_JOB 是为了向后兼容。这是调度程序功能的一个很好的概述。
回答by Raju
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;
/
