SQL & PL/SQL

Hello,

using 10g (10.1.0.2.0) on Windows 2000 I had problems to create scheduler jobs from within a stored procedure (see example below). What easily succeeds using anonymous blocks failed when calling from a stored procedure, due to ORA-27486. Only when I compile the procedure with invoker's rights the call to dbms_Job.Create_Job is successfull!? From my knowledge there is no difference between invoker's and definer's rights, if I compile and call with the same user, is there?

Does anyone know the reason for this behaviour or is it simply a bug?

Have a nice day.
Björn Hachmann
Hamburg / Germany


-- Example start.

create table t
(
a number(1),
b date default sysdate
);

create or replace procedure sched1
is
begin
dbms_scheduler.create_job(
job_name => 'TEST_JOB1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN insert into t values (1); END;',
repeat_interval => 'freq=secondly',
enabled => TRUE
);

commit;
end;
/

create or replace procedure sched2
authid current_user
is
begin
dbms_scheduler.create_job(
job_name => 'TEST_JOB2',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN insert into t values (2); END;',
repeat_interval => 'freq=secondly',
enabled => TRUE
);

commit;
end;
/

exec sched1; -- This call fails with ORA-27486.
exec sched2; -- This call succeeds!

/* Cleanup.

exec dbms_scheduler.drop_job('TEST_JOB1', true);
exec dbms_scheduler.drop_job('TEST_JOB2', true);
drop table t;

*/