Oracle Scheduler - Job and Argument
drop table scheduler_test;
create table scheduler_test(x varchar2(200), y int);
truncate table scheduler_test;
select * from scheduler_test order by 1;
begin
dbms_scheduler.drop_job('ttt');
dbms_scheduler.purge_log(job_name => 'ttt');
end;
/
create or replace procedure p_1(p_x varchar2)is
begin
insert into scheduler_test(x) values(p_x);
commit;
end;
/
当 使用job_type=sp调用一个sp的时候,必须指定所有参数
因为没有定义program,所以只能用 argument_position指定
-- use stored_procedure as job_type and set arguments
declare
l_x integer := 0;
begin
dbms_scheduler.create_job(job_name => 'ttt',
job_type => 'stored_procedure',
job_action => 'p_1',
number_of_arguments => 1,
start_date => systimestamp,
repeat_interval => 'freq=minutely;interval=1;bysecond=0,20,40');
dbms_scheduler.set_job_argument_value('ttt',argument_position => 1,argument_value => to_char(systimestamp));
dbms_scheduler.enable('ttt');
end;
/
如 果job_type=plsql_block调用sp,不能通过set_job_argument_value指定参数
但是可以构造 字符串。并且可以使用Metadata Attributes来作为参数(如下的job_start)
-- use plsql_block as job_type and use scheduler metadata as arguments
declare
l_x integer := 0;
begin
dbms_scheduler.create_job(job_name => 'ttt',
job_type => 'plsql_block',
job_action => 'p_1(job_start);',
start_date => systimestamp,
repeat_interval => 'freq=minutely;interval=1;bysecond=10');
dbms_scheduler.enable('ttt');
end;
/
posted on 2012-01-30 18:20 wait4friend 阅读(399) 评论(0) 编辑 收藏 举报