第29次活动：2017-11-01（星期三晚上7:00-9:30）
DROP TABLE scheduler_test;DROP SEQUENCE scheduler_test_seq;
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
description VARCHAR2(20)NOT NULL,
created_dateDATE NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
--------
CREATE SEQUENCE scheduler_test_seq;
-----
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'test_program_1',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step2');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_2',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step3');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_3',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step4');
END;
BEGIN
sys.dbms_scheduler.create_chain(
comments => 'chain1',
chain_name => '"HR"."CHAIN1"');
sys.dbms_scheduler.define_chain_event_step(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP1"',
event_schedule_name => '"HR"."SCHEDULE4"');
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP1"',
attribute => 'pause',
value => FALSE);
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP1"',
attribute => 'skip',
value => FALSE);
sys.dbms_scheduler.define_chain_step(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP2"',
program_name => '"HR"."TEST_PROGRAM_1"');
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP2"',
attribute => 'pause',
value => FALSE);
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP2"',
attribute => 'skip',
value => FALSE);
sys.dbms_scheduler.define_chain_step(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP3"',
program_name => '"HR"."TEST_PROGRAM_2"');
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP3"',
attribute => 'pause',
value => FALSE);
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP3"',
attribute => 'skip',
value => FALSE);
sys.dbms_scheduler.define_chain_step(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP4"',
program_name => '"HR"."TEST_PROGRAM_3"');
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP4"',
attribute => 'pause',
value => FALSE);
sys.dbms_scheduler.alter_chain(
chain_name => '"HR"."CHAIN1"',
step_name => '"STEP4"',
attribute => 'skip',
value => FALSE);
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => '1=1',
rule_name => 'RULE1',
comments => 'Rule1',
action => 'START Step1');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => 'Step1 SUCCEEDED',
rule_name => 'RULE2',
comments => 'Rule2',
action => 'START Step2');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => 'Step2 SUCCEEDED',
rule_name => 'RULE3',
comments => 'Rule3',
action => 'START Step3');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => 'Step3 SUCCEEDED',
rule_name => 'RULE4',
comments => 'Rule4',
action => 'START Step4');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => 'Step4 SUCCEEDED',
rule_name => 'RULE5',
comments => 'Rule5',
action => 'END');
sys.dbms_scheduler.enable('"HR"."CHAIN1"');
END;组装chain作业（普通的基于时间的Schedule）：
select* from dba_scheduler_global_attribute;
select* from dba_scheduler_notifications;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_server','station90.example.com');
end;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_sender','oracle@example.com');
end;
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB5',
events =>'job_succeeded,job_completed',
recipients =>'oracle@example.com');
end;
begin
DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB5');
end;
错误
create or replace procedure hr.proc1
begin
update t123456 set a=2;
end;
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB51',
recipients =>'oracle@example.com');
end;
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name =>'test_notification_job',
recipients =>'oracle@example.com',
events =>'job_failed',
filter_condition => ':event.error_code=600');
END;
/
BEGIN
sys.dbms_scheduler.disable( '"HR"."JOB51"' );
sys.dbms_scheduler.set_attribute( name => '"HR"."JOB51"', attribute => 'raise_events', value => 0);
sys.dbms_scheduler.enable( '"HR"."JOB51"' );
END;
页:
[1]