Thanks for the question, Pawan.

Asked: October 07, 2022 - 12:07 pm UTC

Last updated: September 01, 2023 - 12:54 pm UTC

Version: Oracle Database 19c EE Extreme Pref Release 19.0.0.0.0 - Production

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am getting the ORA-27486 insufficient privileges issue when i try to execute the DBMS_JOBS.SUBMIT inside a proc like below

CREATE OR REPLACE PROCEDURE PR_TEST AS
 jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(job       => jobno,
                  what      => 'begin dbms_output.put_line(''test'') end;');
  COMMIT;
END;

But When I Try to execute the same code without any proc like below then it executed successfully.

begin
 jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(job       => jobno,
                  what      => 'begin dbms_output.put_line(''test'') end;');
  COMMIT;
END;

Please help me to resolve the issue as i need to execute the JOB from a procedure.

Thank in advance
Pawan

and Chris said...

You probably have access via a role. To call a program unit in compiled PL/SQL you need to grant the necessary privileges to your database user directly.

To easily check if you have privileges through a role, disable them all and try the anonymous block:

set role none;

begin
  jobno number;
begin
  dbms_job.submit(job       => jobno,
                  what      => 'begin dbms_output.put_line(''test'') end;');

end;

set role all;

If this fails, check whether your user has execute on dbms_job and the create job privilege.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Create job and execute on dbms_job are given to schema but not enough?

Alexey, September 01, 2023 - 7:15 am UTC

Have no idea why, but still cannot submit job due to
ORA-27486: insufficient privileges ORA-06512: at
“SYS.DBMS_ISCHED”, line 9394
…
Schema is granted to create job directly and via role
And also granted to execute on DBMS_JOB, DBMS_ISCHED, DBMS_SCHEDULER

What is missing?

September 01, 2023 - 12:54 pm UTC

What exactly is the statement that fails?