0

my question is about using DBMS_SCHEDULER inside a package. I have given the SCHEDULER_ADMIN privileges to the user. Now I can set these commands inside my procedure after I have created the jobs via script outside the procedure.

 -- set Job-Parameters
  DBMS_SCHEDULER.SET_ATTRIBUTE(
   name                     => jobidentifier,
   attribute                => 'job_action',
   value                    => jobparam);
   --  Job run.
   DBMS_SCHEDULER.ENABLE(jobidentifier);

So far everything went well.

Now my question: Why don’t these commands work inside the code of my procedure?

 -- Stop Job
DBMS_SCHEDULER.STOP_JOB (
   job_name                 =>  jobidentifier,
   force                    =>  true);

  -- disable Job
DBMS_SCHEDULER.DISABLE(jobidentifier);

As soon as I enter the commands in an own session via script and not embedded in the package-code the commands work. The problem is I want to enter these commands inside my PL-SQL-package, cause inside my package I know when there occurs an error, so I want to enter these commands.

Can you help me? Thank you very much.

I'm getting the following error:

ORA-27486: Nicht ausreichende Berechtigungen ORA-06512: in "SYS.DBMS_ISCHED", Zeile 210 ORA-06512: in "SYS.DBMS_SCHEDULER", Zeile 557 ORA-06512: in "SCHEMANAME.JOB_KILLEN_PCK", Zeile 31 ORA-06512: in Zeile 6

the Grant I made was given by the sys_user: 

GRANT SCHEDULER_ADMIN TO SCHEMANAME

in line 31 there is the following code 

DBMS_SCHEDULER.STOP_JOB ( job_name => jobname, force => true);

Is this for securtiy reasons that Oracle does not want me to be able to stop_jobs by starting a Procedure. Cause this way I could stop each Job I know the name? If this is correct I will be unable to stop the Jobs in case of failures occurring during the Jobs in my procedure

If you want I can send you my test-Packages

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Armin Höfling
  • 1
  • 1
  • 4

2 Answers2

1

I think stop_job with the force option requires the MANAGE SCHEDULER system privilege granted to the user owning the job.

Hope this helps.

@balakshman

Lakshman Bulusu
  • 11
  • 1
0

Please don't use Force option and you are good, please find below example it works well with package integration.

DECLARE
V_COUNT NUMBER(3);
BEGIN
    DBMS_SCHEDULER.STOP_JOB(job_name=>'ORDER_REPORT_JOB_1');
    SELECT COUNT(*) INTO V_COUNT FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'ORDER_REPORT_JOB_1';
    --THIS ORDER_REPORT_JOB_1 EXIST THEN ONLY DROP THE JOB
    IF (V_COUNT=1) THEN
        DBMS_SCHEDULER.DROP_JOB ('ORDER_REPORT_JOB_1');
    END IF; 
END;
/
Sanjay Jain
  • 651
  • 1
  • 8
  • 10