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. I am back on 28. of September.

SQL> select * from dba_sys_privs
  2  where grantee = 'SCHEDULER_ADMIN';

GRANTEE         PRIVILEGE                                ADM COM
--------------- ---------------------------------------- --- ---
SCHEDULER_ADMIN CREATE JOB                               YES YES
SCHEDULER_ADMIN EXECUTE ANY CLASS                        YES YES
SCHEDULER_ADMIN CREATE CREDENTIAL                        YES YES
SCHEDULER_ADMIN CREATE ANY CREDENTIAL                    YES YES
SCHEDULER_ADMIN EXECUTE ANY PROGRAM                      YES YES
SCHEDULER_ADMIN CREATE ANY JOB                           YES YES
SCHEDULER_ADMIN MANAGE SCHEDULER                         YES YES
SCHEDULER_ADMIN CREATE EXTERNAL JOB                      YES YES

SQL> create user DEMO identified by DEMO;

User created.

SQL>
SQL> grant connect, create job , manage scheduler, create procedure to demo;

Grant succeeded.

SQL> grant execute on dbms_lock to demo;

Grant succeeded.

SQL>
SQL> connect DEMO/DEMO
Connected.

SQL>
SQL> BEGIN
  2    DBMS_SCHEDULER.create_job (
  3      job_name        => 'MY_JOB',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'BEGIN for i in 1 .. 60 loop dbms_lock.sleep(1); end loop; END;',
  6      start_date      => sysdate,
  7      enabled         => TRUE);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> create or replace
  2  package DEMO.PKG is
  3    procedure stopper;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace
  2  package body DEMO.PKG is
  3    procedure stopper is
  4    begin
  5      dbms_scheduler.stop_job('DEMO.MY_JOB',force=>true);
  6    end;
  7  end;
  8  /

Package body created.

SQL>
SQL> exec pkg.stopper;

PL/SQL procedure successfully completed.