oracle dbms_scheduler.create_job() error when executed within stored procedure

Question

Creating job from anonymous block is working fine:

begin
  SYS.DBMS_SCHEDULER.create_job(
  job_name    =>     'test_job',
  job_type    =>     'PLSQL_BLOCK',
  job_action  =>     'begin null; end;',
  enabled     =>     TRUE,
  auto_drop   =>      TRUE);
end;

Result:

PL/SQL procedure successfully completed.

Creating job from stored procedure:

create or replace procedure pr_create_job is
begin
  SYS.DBMS_SCHEDULER.create_job(
  job_name =>'test_job',
  job_type =>'PLSQL_BLOCK',
  job_action =>'begin null; end;',
  enabled     => TRUE,
  auto_drop   => TRUE);
end pr_create_job;

Begin
  pr_create_job;
End;

Result:

Error report - ORA-27486: insufficient privileges ORA-06512: at "SYS.DBMS_ISCHED", line 135 ORA-06512: at "SYS.DBMS_SCHEDULER", line 271 ORA-06512: at "PR_CREATE_JOB", line 3 ORA-06512: at line 2 27486. 00000 - "insufficient privileges"

Probably because privilege to use dbms_scheduler is granted to the user through a role. It should be granted directly to the user. — Kaushik Nayak

waldemort waldemort · Accepted Answer · 2019-03-21T13:31:13

As Kaushik Nayak mentioned, CREATE JOB privilege was granted through a role and not directly to the user, granting directly to user solved this error.

oracle dbms_scheduler.create_job() error when executed within stored procedure

1 Answers