CODEWITHSUNDEEP
sqloracle-databasestored-proceduresdbms-scheduler
jvantine
jvantine

Reputation: 1

Using DBMS_SCHEDULER.CREATE_JOB within a procedure

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?

Upvotes: 0

Views: 3869

Answers (1)

Nafaz M N M
Nafaz M N M

Reputation: 1688

execute the following commands under the admin privileges

   sys as sysdba

grant execute on DBMS_SCHEDULER to USERNAME;
grant create job to USERNAME;

Upvotes: 1

Related Questions