I am trying to schedule a job using DBMS_JOB (I can't use DBMS_SCHEDULER for security reasons), which uses a DDL statement.
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;
/
It fails to execute giving me an error message :
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;
If I execute it standalone, it obviously executes. The only time it fails it when I try to execute the entire thing through the call to DBMS_JOB.RUN().
Is there a restriction on using DDL statements as a parameter in DBMS_JOB? I can't find any pointer in documentation for this.
While echoing the sentiments of the other commenters-- creating tables on the fly is a red flag that often indicates that you really ought to be using global temporary tables-- a couple of questions.
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'.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.