0

I am trying to create a procedure that will perform certain updates on a couple tables and then schedule another procedure to run later to revert them back. I'm running into a problem in using the DBMS_SCHEDULER.CREATE_JOB. My code looks like this:

create or replace procedure My_Procedure(p_DUNS in varchar2, p_PLUS_FOUR in varchar2) is

*SOME VARIABLES*

Begin

*DO SOME STUFF*

p_JOB_ACTION := 'begin run_other_procedure(' || p_VENDOR_ID || ', ' || p_CCRID || ', ' || nvl(to_char(p_INACTIVE_DATE),'null') || ',' || nvl(to_char(p_END_DATE),'null')  || '); END;';

DBMS_SCHEDULER.CREATE_JOB(job_name        => 'DEACTIVE_VENDOR_'||to_char(p_CCRID),
                      job_type        => 'PLSQL_BLOCK',
                      JOB_ACTION      => p_JOB_ACTION,
                      start_date      => sysdate+1,
                      enabled         => TRUE,
                      comments        => 'Calls PLSQL once');

END;

I have verified the beginning portion of the procedure, but when I add in the CREATE_JOB I'm getting the below error:

Error starting at line : 1 in command -
execute MY_PROCEDURE('140986105', null)
Error report -
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at "CGEXT.MY_PROCEDURE", line 63
ORA-06512: at line 1
27486. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a scheduler operation without the
           required privileges.
*Action:   Ask a sufficiently privileged user to perform the requested
           operation, or grant the required privileges to the proper user(s).

When I pull out the CREATE_JOB and run it by itself I have no problems.

Any thoughts on this?

jvantine
  • 1
  • 1
  • 4
  • 6
    Your Oracle user most probably gets the privilege to use `dbms_scheduler` through a role. Privileges granted through a role are not in effect in PL/SQL. The user needs to be granted the privilege directly –  Apr 09 '15 at 20:36
  • ask your DB admin to 1. grant the privilegies 2. configure the scheduler (there are settings like concurrent job count etc.) – Pavel Gatnar Apr 10 '15 at 08:17
  • The following was run to grant privileges:DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, 'CGEXT'); two other grants as well. Is there anything else to grant this role to the user? – jvantine Apr 10 '15 at 19:22

1 Answers1

1

execute the following commands under the admin privileges

   sys as sysdba

grant execute on DBMS_SCHEDULER to USERNAME;
grant create job to USERNAME;
Nafaz M N M
  • 1,558
  • 2
  • 27
  • 41