Question

我试图使用 DBMS_JOB(出于安全原因,我不能使用 DBMS_SCHEDULER) 来安排一份工作, 该工作使用 DDL 语句。

DECLARE
job_num NUMBER;
BEGIN
DBMS_JOB.SUBMIT(job => job_num,
what =>  BEGIN EXECUTE IMMEDIATE   CREATE TABLE temp1 (ID NUMBER)  ; END; 
);
DBMS_OUTPUT.PUT_LINE( JobID ||job_num);
DBMS_JOB.RUN(job_num);
END;
/

它没有执行给我错误信息 :

ORA-12011: execution of 1 jobs failed ORA-06512: at "SYS.DBMS_IJOB", line 548 ORA-06512: at "SYS.DBMS_JOB", line 278 ORA-06512: at line 8

On removing the DBMS_JOB.RUN() statement from inside the anonymous block, I am able to at least create (and save) the job. When I check the job, it has saved this as the code to execute BEGIN EXECUTE IMMEDIATE CREATE TABLE temp1 (id NUMBER) ; END;

如果我独立执行它,它显然会执行。它只有在我试图通过 DBMS_JOB.RUN () 的电话执行整件事时,它才会失败。

在 DBMS_JOB 中, DDL 语句是否限制用作参数? 在文档中找不到任何指针。

Answer 1

在回应其他评论者的意见的同时 -- 在苍蝇上创建表格是一个红旗, 经常表明你真的应该使用全球临时表格 -- 几个问题。

Is there a reason that you need the DBMS_JOB.RUN call? Your call to DBMS_JOB.SUBMIT is telling Oracle to run the job asynchronously as soon as the parent transaction commits. So, normally, you d call DBMS_JOB.SUBMIT and then just `COMMIT .
Does the user that is submitting job have the CREATE TABLE privilege granted directly? My guess is that the user only has the CREATE TABLE privilege granted via a role. That would allow you to run the anonymous PL/SQL block interactively but not in a job. If so, you ll need the DBA to grant you the CREATE TABLE privilege directly, not via a role.
When a job fails, an entry is written to the alert log with the error message. Can you (or, more likely, the DBA) get the error message and the error stack from the alert log and post it here (assuming it is something other than the privileges issue from #2).

友情链接