Notice: This blog is written by Bo Tang.
------job5
- select * from dba_scheduler_global_attribute;
- select * from dba_scheduler_notifications;
- begin
- DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
- ('email_server','192.168.0.79');
- 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;
- 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',
- events => 'job_failed')
- ;
- END;
- -
复制代码
Bug 16623661
---job6
- select * from hr.scheduler_test;
- DROP TABLE hr.scheduler_test;
- DROP SEQUENCE hr.scheduler_test_seq;
- CREATE TABLE hr.scheduler_test (
- id NUMBER(10) NOT NULL,
- description VARCHAR2(20) NOT NULL,
- created_date DATE NOT NULL,
- CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
- );
- CREATE SEQUENCE hr.scheduler_test_seq;
- select * from hr.scheduler_test;
- DECLARE
- l_enqueue_options DBMS_AQ.enqueue_options_t;
- l_message_properties DBMS_AQ.message_properties_t;
- l_message_handle RAW(16);
- l_queue_msg sys.t_event_queue_payload;
- BEGIN
- l_queue_msg := sys.t_event_queue_payload('Chemical_Wrong');
- DBMS_AQ.enqueue(queue_name => 'sys.event_queue',
- enqueue_options => l_enqueue_options,
- message_properties => l_message_properties,
- payload => l_queue_msg,
- msgid => l_message_handle);
- COMMIT;
- END;
复制代码 -----------------------------------
如果HR本人,需要以下权限
BEGIN DBMS_RULE_ADM.grant_system_privilege( privilege =>DBMS_RULE_ADM.create_rule_set_obj, grantee => 'HR', grant_option => FALSE); DBMS_RULE_ADM.grant_system_privilege( privilege =>DBMS_RULE_ADM.create_evaluation_context_obj, grantee => 'HR', grant_option => FALSE); DBMS_RULE_ADM.grant_system_privilege( privilege => DBMS_RULE_ADM.create_rule_obj, grantee => 'HR', grant_option => FALSE); END;
--------------------------------------------------
规则编程的条件语法示例:
- ':step1.state=''SUCCEEDED'' and :step2.error_code=3'
- '1=1'
- ':step3.state != ''COMPLETED'''
- ':step2.error_code not in (1,2,3)'
- ':step1.state = ''NOT_STARTED'''
复制代码
--------------------
- BEGIN
- DBMS_SCHEDULER.create_program (
- program_name => 'hr.test_program_1',
- program_type => 'PLSQL_BLOCK',
- program_action => 'BEGIN
- INSERT INTO hr.scheduler_test (id, description, created_date)
- VALUES (hr.scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
- COMMIT;
- END;',
- enabled => TRUE,
- comments => 'Step2');
- DBMS_SCHEDULER.create_program (
- program_name => 'hr.test_program_2',
- program_type => 'PLSQL_BLOCK',
- program_action => 'BEGIN
- INSERT INTO hr.scheduler_test (id, description, created_date)
- VALUES (hr.scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
- COMMIT;
- END;',
- enabled => TRUE,
- comments => 'Step3');
- DBMS_SCHEDULER.create_program (
- program_name => 'hr.test_program_3',
- program_type => 'PLSQL_BLOCK',
- program_action => 'BEGIN
- INSERT INTO hr.scheduler_test (id, description, created_date)
- VALUES (hr.scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
- COMMIT;
- END;',
- enabled => TRUE,
- comments => 'Step4');
- END;
复制代码
test_chain:
- BEGIN
- DBMS_SCHEDULER.run_chain (
- chain_name => 'test_chain_1',
- job_name => 'test_chain_1_run_job',
- start_steps => 'chain_step_1, chain_step_2, chain_step_3');
- END;
复制代码
|