/*
|| Oracle 10gR2 Scheduler Enhancements Listing 2
||
|| Demonstrates new Oracle 10gR2 Scheduler features, including:
|| - Examples of Job Chain objects
|| - Examples of Job Chain Rulesets
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10gR2
|| Scheduling features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/
-----
-- Listing 2.1: Create HR.XT_EMPLOYEE_PAYCHECKS for validation of
-- flat file shipped from vendor containing bimonthly
-- payroll check transactions
-----
-- Create directory object for payroll transaction files
DROP DIRECTORY ftp_secured;
CREATE DIRECTORY ftp_secured
AS 'Z:\_dba\ftp_secured';
GRANT READ,WRITE ON DIRECTORY ftp_secured TO hr;
-- Create table
DROP TABLE hr.xt_employee_paychecks;
CREATE TABLE hr.xt_employee_paychecks (
paycheck_id NUMBER(10)
,employee_id NUMBER(10)
,pay_period CHAR(10)
,pay_date CHAR(10)
,total_amt NUMBER(15,2)
,salary_amt NUMBER(15,2)
,fit_wth_amt NUMBER(15,2)
,ssa_wth_amt NUMBER(15,2)
,mca_wth_amt NUMBER(15,2)
,sit_wth_amt NUMBER(15,2)
,rtr_pln_amt NUMBER(15,2)
,ins_ded_amt NUMBER(15,2)
,comments VARCHAR2(128)
,status_ind CHAR(1)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ftp_secured
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
(
paycheck_id char(10)
,employee_id char(10)
,pay_period CHAR(10)
,pay_date CHAR(10)
,total_amt char(15)
,salary_amt CHAR(15)
,fit_wth_amt CHAR(15)
,ssa_wth_amt CHAR(15)
,mca_wth_amt CHAR(15)
,sit_wth_amt CHAR(15)
,rtr_pln_amt CHAR(15)
,ins_ded_amt CHAR(15)
,comments CHAR(128)
,status_ind CHAR(1)
)
)
LOCATION('bimonthly_payroll.dat')
);
-----
-- Listing 2.2: Create table (HR.EMPLOYEE_PAYCHECKS) that contains all
-- bimonthly payroll transactions
-----
DROP TABLE hr.employee_paychecks CASCADE CONSTRAINTS PURGE;
CREATE TABLE hr.employee_paychecks (
paycheck_id NUMBER(10) NOT NULL
,employee_id NUMBER(10) NOT NULL
,pay_period DATE NOT NULL
,pay_date DATE NOT NULL
,total_amt NUMBER(15,2) DEFAULT 0 NOT NULL
,salary_amt NUMBER(15,2) DEFAULT 0 NOT NULL
,fit_wth_amt NUMBER(15,2) DEFAULT 0
,ssa_wth_amt NUMBER(15,2) DEFAULT 0
,mca_wth_amt NUMBER(15,2) DEFAULT 0
,sit_wth_amt NUMBER(15,2) DEFAULT 0
,rtr_pln_amt NUMBER(15,2) DEFAULT 0
,ins_ded_amt NUMBER(15,2) DEFAULT 0
,comments VARCHAR2(128) DEFAULT 'Thank you for your hard work!'
,status_ind CHAR(1) DEFAULT 'P' NOT NULL
)
TABLESPACE example
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
-- Create comments
COMMENT ON TABLE hr.employee_paychecks
IS 'Describes Employee Payroll Checks master domain';
COMMENT ON COLUMN hr.employee_paychecks.paycheck_id
IS 'Payroll Check Number. Unique identifier for Payroll Check';
COMMENT ON COLUMN hr.employee_paychecks.employee_id
IS 'Employee ID. See the Employees entity for a detailed description of this attribute';
COMMENT ON COLUMN hr.employee_paychecks.pay_period
IS 'Payroll Pay Period Date';
COMMENT ON COLUMN hr.employee_paychecks.pay_date
IS 'The date on which this Paycheck will be deposited in the Employee''s bank account, or will be cashable (if a paper check)';
COMMENT ON COLUMN hr.employee_paychecks.total_amt
IS 'Paycheck Total Amount';
COMMENT ON COLUMN hr.employee_paychecks.salary_amt
IS 'Paycheck Salary Amount';
COMMENT ON COLUMN hr.employee_paychecks.fit_wth_amt
IS 'Paycheck Federal Withholding Amount';
COMMENT ON COLUMN hr.employee_paychecks.ssa_wth_amt
IS 'Paycheck FICA Withheld Amount';
COMMENT ON COLUMN hr.employee_paychecks.mca_wth_amt
IS 'Paycheck Medicare Withheld Amount';
COMMENT ON COLUMN hr.employee_paychecks.sit_wth_amt
IS 'Paycheck State Tax Withheld Amount';
COMMENT ON COLUMN hr.employee_paychecks.rtr_pln_amt
IS 'Paycheck Retirement Plan Contribution Amount';
COMMENT ON COLUMN hr.employee_paychecks.ins_ded_amt
IS 'Paycheck Insurance / Medical Plan Payments Amount';
COMMENT ON COLUMN hr.employee_paychecks.comments
IS 'Paycheck Commentary. Contains a thank you note unless overridden';
COMMENT ON COLUMN hr.employee_paychecks.status_ind
IS 'Status Indicator. Indicates whether Payroll Check is a (P)aper check, (E)TF, or (O)ther';
-- Create indexes and constraints
CREATE UNIQUE INDEX hr.payroll_checks_pk_idx
ON hr.employee_paychecks(paycheck_id)
TABLESPACE example
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
ALTER TABLE hr.employee_paychecks
ADD CONSTRAINT paycheck_status_ck
CHECK (status_ind IN ('P','E','O'));
ALTER TABLE hr.employee_paychecks
ADD CONSTRAINT employee_paychecks_pk
PRIMARY KEY (paycheck_id);
ALTER TABLE hr.employee_paychecks
ADD CONSTRAINT employee_paychecks_emp_fk
FOREIGN KEY (employee_id)
REFERENCES hr.employees (employee_id);
DROP PUBLIC SYNONYM employee_paychecks;
CREATE PUBLIC SYNONYM employee_paychecks FOR hr.employee_paychecks;
-----
-- Listing 2.3: Create package that handles all business functions
-- for bimonthly payroll processing
-----
GRANT EXECUTE ON sys.utl_mail TO hr;
-- Create Error Logging table to contain any failures during DML processing
DROP TABLE hr.payroll_processing_errors PURGE;
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG(
dml_table_name => 'HR.EMPLOYEE_PAYCHECKS'
,err_log_table_name => 'PAYROLL_PROCESSING_ERRORS'
,err_log_table_owner => 'HR'
,err_log_table_space => 'EXAMPLE'
);
END;
/
-- Package specification
CREATE OR REPLACE PACKAGE hr.payroll_processing
/*
|| Package Specification: hr.payroll_processing
|| Purpose: Specifies public interface for all payroll processing
|| business functions.
|| Version: 1.0
|| Author: JSC (G+R)
*/
IS
PROCEDURE validate_payroll_transactions;
PROCEDURE load_payroll_transactions;
PROCEDURE post_payroll_reporting;
PROCEDURE email_notification (
notification_type CHAR
);
END payroll_processing;
/
-- Package body
CREATE OR REPLACE PACKAGE BODY hr.payroll_processing
/*
|| Package Body: hr.payroll_processing
|| Purpose: Implements public interface for all payroll processing
|| business functions.
|| Version: 1.0
|| Author: JSC (G+R)
*/
IS
PROCEDURE validate_payroll_transactions
/*
|| Procedure: validate_payroll_transactions
|| Arguments: None
|| Purpose: Performs validation of incoming payroll transactions
|| before they are loaded into the HR.EMPLOYEE_PAYCHECKS
|| table. If any errors are detected, an e-mail is sent
|| to the appropriate developer so that problem resolution
|| can begin.
*/
IS
error_count INTEGER := 0;
failed_validation EXCEPTION;
BEGIN
SELECT COUNT(*)
INTO error_count
FROM hr.xt_employee_paychecks
WHERE status_ind NOT IN ('E', 'O', 'P');
IF error_count > 0 THEN
RAISE failed_validation;
END IF;
EXCEPTION
WHEN failed_validation THEN
RAISE_APPLICATION_ERROR(
-20001
,'Validation of Employee Payroll Check file detects ' || error_count || ' errors.'
);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(
-20099
,'Serious unknown error during payroll validation. Inform your DBA immediately!'
);
END validate_payroll_transactions;
PROCEDURE load_payroll_transactions
/*
|| Procedure: load_payroll_transactions
|| Arguments: None
|| Purpose: Loads payroll transactions into HR.EMPLOYEE_PAYCHECKS
|| table. Note that any failed DML is automatically
|| logged to HR.PAYROLL_PROCESSING_ERRORS via 10gR2
|| DML error logging features.
*/
IS
error_count INTEGER := 0;
failed_loading EXCEPTION;
BEGIN
-- Load rows into table
BEGIN
INSERT INTO hr.employee_paychecks (
paycheck_id
,employee_id
,pay_period
,pay_date
,total_amt
,salary_amt
,fit_wth_amt
,ssa_wth_amt
,mca_wth_amt
,sit_wth_amt
,rtr_pln_amt
,ins_ded_amt
,comments
,status_ind
)
SELECT
paycheck_id
,employee_id
,TO_DATE(pay_period,'mm/dd/yyyy')
,TO_DATE(pay_date,'mm/dd/yyyy')
,total_amt
,salary_amt
,fit_wth_amt
,ssa_wth_amt
,mca_wth_amt
,sit_wth_amt
,rtr_pln_amt
,ins_ded_amt
,comments
,status_ind
FROM hr.xt_employee_paychecks
LOG ERRORS INTO HR.PAYROLL_PROCESSING_ERRORS(TO_CHAR(SYSDATE,'yyyymmdd') || 'Payroll Loading')
REJECT LIMIT UNLIMITED;
COMMIT;
END;
-- Check for any failed DML transactions
SELECT COUNT(*)
INTO error_count
FROM hr.payroll_processing_errors
WHERE ora_err_tag$ = TO_CHAR(SYSDATE,'yyyymmdd') || 'Payroll Loading';
IF error_count > 0 THEN
RAISE failed_loading;
END IF;
EXCEPTION
WHEN failed_loading THEN
RAISE_APPLICATION_ERROR(
-20002
,'DML errors encountered during Employee Payroll Check load for cycle period ' || TO_CHAR(SYSDATE,'yyyymmdd')
);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(
-20099
,'Serious unknown error during payroll loading. Inform your DBA immediately!'
);
END load_payroll_transactions;
PROCEDURE post_payroll_reporting
/*
|| Procedure: post_payroll_processing
|| Arguments: None
|| Purpose: Validates newly-loaded payroll transactions in
|| HR.EMPLOYEE_PAYCHECKS and, if any errors are detected,
|| sends an e-mail to the appropriate business unit
|| to inform them of the issues.
*/
IS
error_count INTEGER := 0;
failed_reporting EXCEPTION;
BEGIN
SELECT COUNT(*)
INTO error_count
FROM hr.employee_paychecks
WHERE total_amt <>
(salary_amt - (fit_wth_amt
+ ssa_wth_amt
+ mca_wth_amt
+ sit_wth_amt
+ rtr_pln_amt
+ ins_ded_amt));
IF error_count > 0 THEN
RAISE failed_reporting;
END IF;
EXCEPTION
WHEN failed_reporting THEN
RAISE_APPLICATION_ERROR(
-20003
,'Employee Payroll Checks post-load verfication has found '
|| error_count || ' checks that are not in balance.'
);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(
-20099
,'Serious unknown error during post-validation payroll processing. Inform your DBA immediately!');
END post_payroll_reporting;
PROCEDURE email_notification (
notification_type CHAR
)
/*
|| Procedure: email_notification
|| Arguments: notification_type: Either (V)alidation or (R)eporting
|| Purpose: Issues e-mail for failed payroll processing operations
*/
IS
CRLF CHAR(2) := CHR(10) || CHR(13);
subject VARCHAR2(128);
message VARCHAR2(512);
BEGIN
CASE UPPER(notification_type)
WHEN 'V' THEN
BEGIN
subject := 'Failure during validation of Employee Payroll Checks File';
message := 'To Whom It May Concern: ' || CRLF || CRLF
|| 'The bimonthly employee payroll checks external file '
|| ' has failed its preliminary validation.' || CRLF || CRLF
|| 'Kind regards,' || CRLF || CRLF
|| 'Your tireless Database Admininstrators';
END;
WHEN 'R' THEN
BEGIN
subject := 'Warning: Employee Payroll Checks May Be Out Of Balance';
message := 'To Whom It May Concern: ' || CRLF || CRLF
|| 'The bimonthly employee payroll checks most recently processed'
|| ' seem to be out of balance. Please verify this at your earliest convenience.'
|| 'Kind regards,' || CRLF || CRLF
|| 'Your tireless Database Admininstrators';
END;
END CASE;
-- Notify the accounting department of the validation failure
UTL_MAIL.SEND(
sender => 'dba@yourcompany.com'
,recipients => 'actgsupv@yourcompany.com'
,cc => NULL
,bcc => NULL
,subject => subject
,message => message
,mime_type => 'text/plain; charset=us-ascii'
,priority => 1
);
END email_notification;
END payroll_processing;
/
-----
-- Listing 2.4: Set up new event queues, agents, and subcribers
-- so that arrival of new external files can be
-- registered and detected
-----
-----
-- Grant appropriate system and object permissions to
-- HR user account:
-----
GRANT AQ_ADMINISTRATOR_ROLE TO hr;
GRANT AQ_USER_ROLE TO hr;
GRANT MANAGE SCHEDULER TO hr;
GRANT CREATE JOB TO hr;
GRANT EXECUTE ON SYS.SCHEDULER$_EVENT_INFO TO hr;
GRANT EXECUTE ON SYS.DBMS_AQ TO hr;
GRANT EXECUTE ON SYS.DBMS_AQADM TO hr;
DECLARE
subscriber SYS.AQ$_AGENT;
BEGIN
-----
-- Create and start a new Event Queue. This new queue will monitor the
-- Scheduler Event Queue Table (SYS.SCHEDULER$_EVENT_QTAB) for any
-- pertinent events
-----
-- Create a new queue
DBMS_AQADM.CREATE_QUEUE (
queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
,queue_table => 'SYS.SCHEDULER$_EVENT_QTAB'
);
-- Start the newly-created queue
DBMS_AQADM.START_QUEUE (
queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
);
-----
-- Add a new Suscriber, SYS.agt_file_arrival_monitor, to the newly-created
-- Event Queue
-----
subscriber :=
SYS.AQ$_AGENT(
name => 'agt_file_arrival_monitor'
,address => NULL
,protocol => NULL
);
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
,subscriber => subscriber);
-----
-- Allow the NEWHIRE_AGENT to log into the database using
-- the HR user account
-----
DBMS_AQADM.ENABLE_DB_ACCESS(
agent_name => 'agt_file_arrival_monitor'
,db_username => 'HR');
-----
-- Grant the HR user account the appropriate privileges to
-- place information in the SYS.NEWHIRE_EVENT_Q queue
-----
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(
privilege => 'ENQUEUE'
,queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
,grantee => 'HR'
,grant_option => FALSE);
END;
/
-----
-- Listing 2.5: Create a new Job Chain, Chain Steps, and Chain Rules
-- to handle Employee Payroll Checks bimonthly processing
-----
-----
-- Create a new Job Chain object
-----
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
chain_name => 'hr.bimonthly_payroll_processing'
,rule_set_name => NULL
,evaluation_interval => NULL
,comments => 'Drives business logic for processing bimonthly payroll check processing'
);
END;
/
-----
-- Create new Job Chain Steps
-----
BEGIN
-- Step 1: Accept arrival of incoming Payroll Checks external file
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
chain_name => 'hr.bimonthly_payroll_processing'
,step_name => 'file_arrival'
,event_schedule_name => 'hr.monitor_paycheck_arrival'
);
-- Step 2: Verify incoming Payroll Checks external file
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'hr.bimonthly_payroll_processing'
,step_name => 'payroll_validation'
,program_name => 'hr.pgm_validate_payroll_data'
);
-- Step 3: Load Payroll Checks data from external file
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'hr.bimonthly_payroll_processing'
,step_name => 'load_payroll_checks'
,program_name => 'hr.pgm_load_payroll_data'
);
-- Step 4: Perform post-loading validation
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'hr.bimonthly_payroll_processing'
,step_name => 'post_load_reporting'
,program_name => 'hr.pgm_post_load_reports'
);
-- Error Step: Notifies appropriate persons when payroll
-- validation step has failed
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'hr.bimonthly_payroll_processing'
,step_name => 'notify_valid_failure'
,program_name => 'hr.pgm_notify_validation'
);
-- Error Step: Notifies appropriate persons when payroll
-- loading step has failed
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'hr.bimonthly_payroll_processing'
,step_name => 'notify_load_failure'
,program_name => 'hr.pgm_notify_load'
);
-- Error Step: Notifies appropriate persons when payroll
-- post-load reporting step has failed
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'hr.bimonthly_payroll_processing'
,step_name => 'notify_rptg_failure'
,program_name => 'hr.pgm_notify_rptg'
);
END;
/
-----
-- Create Job Chain Rules
-----
BEGIN
-----
-- Rule: rule_100
-- Precursor: NONE
-- Action: Start validation of external file
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'TRUE'
,action => 'START file_arrival'
,rule_name => 'rule_100'
,comments => 'Starts bimonthly payroll processing'
);
-----
-- Rule: rule_200
-- Precursor: Arrival of paycheck file
-- Action: Start validation of external file
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'TRUE'
,action => 'START payroll_validation'
,rule_name => 'rule_200'
,comments => 'Validates incoming payroll file'
);
-----
-- Rule: rule_210
-- Precursor: Failure during payroll checks external file validation
-- Action: Trigger error notification
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'payroll_validation FAILED'
,action => 'START notify_valid_failure'
,rule_name => 'rule_210'
,comments => 'External file validation failure - triggers notification'
);
-----
-- Rule: rule_211
-- Precursor: Completion of validation failure notification
-- Action: End job with errors
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'notify_valid_failure COMPLETED'
,action => 'END step_200.8'
,rule_name => 'rule_211'
,comments => 'Terminates job with failure code'
);
-----
-- Rule: rule_300
-- Precursor: Successful validation of incoming data
-- Action: Start payroll loading
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'payroll_validation SUCCEEDED'
,action => 'START load_payroll_checks'
,rule_name => 'rule_300'
,comments => 'Starts bimonthly payroll processing'
);
-----
-- Rule: rule_310
-- Precursor: Failure during loading of payroll checks
-- Action: Trigger error notification
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'load_payroll_checks FAILED'
,action => 'START notify_load_failure'
,rule_name => 'rule_310'
,comments => 'Payroll loading failure - triggers notifications'
);
-----
-- Rule: rule_311
-- Precursor: Completion of load failure notification
-- Action: End job with errors
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'notify_load_failure COMPLETED'
,action => 'END step_300.8'
,rule_name => 'rule_311'
,comments => 'Terminates job with failure code'
);
-----
-- Rule: rule_400
-- Precursor: Successful payroll data load
-- Action: Start post-payroll loading verification
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'load_payroll_checks COMPLETED'
,action => 'START post_load_reporting'
,rule_name => 'rule_400'
,comments => 'Starts post-payroll processing reporting'
);
-----
-- Rule: rule_410
-- Precursor: Failure during post-payroll reporting
-- Action: Trigger error notification
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'post_load_reporting FAILED'
,action => 'START notify_load_failure'
,rule_name => 'rule_410'
,comments => 'Payroll loading failure - triggers notifications'
);
-----
-- Rule: rule_311
-- Precursor: Completion of post-payroll reporting failure notification
-- Action: End job with errors
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'post_load_reporting COMPLETED'
,action => 'END step_400.8'
,rule_name => 'rule_411'
,comments => 'Terminates job with failure code'
);
-----
-- Rule: rule_999
-- Precursor: Successful job completion
-- Action: End chain with condition code 0
-----
DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
chain_name => 'hr.bimonthly_payroll_processing'
,condition => 'post_load_reporting SUCCEEDED'
,action => 'END 0'
,rule_name => 'rule_999'
,comments => 'Successful Job Chain ending'
);
END;
/
-----
-- Create Program objects to handle calls from Job Chain steps
-----
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'hr.pgm_load_payroll_data'
,program_type => 'STORED_PROCEDURE'
,program_action => 'hr.payroll_processing.load_payroll_transactions'
,number_of_arguments => 0
,enabled => TRUE
,comments => 'Loads Employee Paycheck data'
);
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'hr.pgm_post_load_reports'
,program_type => 'STORED_PROCEDURE'
,program_action => 'hr.payroll_processing.post_payroll_reporting'
,number_of_arguments => 0
,enabled => TRUE
,comments => 'Performs Employee Paycheck post-payroll reporting'
);
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'hr.pgm_notify_validation'
,program_type => 'PLSQL_BLOCK'
,program_action => 'BEGIN hr.payroll_processing.email_notification(''V''); END;'
,enabled => TRUE
,comments => 'Notifies Accounting personnel when a failure occurs'
);
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'hr.pgm_notify_reporting'
,program_type => 'PLSQL_BLOCK'
,program_action => 'BEGIN hr.payroll_processing.email_notification(''R''); END;'
,enabled => TRUE
,comments => 'Notifies Accounting personnel when a failure occurs'
);/
-----
-- Listing 2.6: Create a new event-driven Schedule object, enable the Job
-- Chain, and create a Job to start the Job Chain
-----
-----
-- Create a new Event Schedule object that will trigger the Job Chain
-- when it detects the arrival of the latest version of the bimonthly
-- payroll file via a queued message
-----
BEGIN
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE(
schedule_name => 'hr.monitor_paycheck_arrival'
,start_date => SYSTIMESTAMP
,event_condition => 'tab.user_data.object_owner = ''HR''
AND tab.user_data.object_name = ''bimonthly_payroll.dat''
AND tab.user_data.event_type = ''FILEARRIVED'''
,queue_spec => 'SYS.FILE_ARRIVAL_EVENT_Q,SYS.AGT_FILE_ARRIVAL_MONITOR'
);
END;
/
-----
-- Enable the Job Chain
-----
BEGIN
DBMS_SCHEDULER.ENABLE(
name => 'hr.bimonthly_payroll_processing'
);
END;
/
-----
-- Create a Job that's scheduled to run the Chain daily at at 6:05 AM
-----
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'hr.chn_start_payroll_processing'
,job_type => 'CHAIN'
,job_action => 'hr.bimonthly_payroll_processing'
,repeat_interval => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=5;BYSECOND=0'
,enabled => TRUE
);
END;
/
-----
-- Listing 2.7: Proof of concept
-----
-----
-- Start the job immediately (outside of its scheduled run time)
-----
BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'hr.chn_start_payroll_processing'
);
END;
/
-----
-- Enqueue an entry into the file arrival event queue to
-- signal that the file has arrived
-----
DECLARE
enq_opt DBMS_AQ.ENQUEUE_OPTIONS_T;
msg_prop DBMS_AQ.MESSAGE_PROPERTIES_T;
rcpt_list DBMS_AQ.AQ$_RECIPIENT_LIST_T;
enq_msgid RAW(16);
userdata SYS.SCHEDULER$_EVENT_INFO;
BEGIN
userdata :=
SYS.SCHEDULER$_EVENT_INFO(
'FILEARRIVED'
,'HR'
,'bimonthly_payroll.dat'
,SYSTIMESTAMP
);
msg_prop.sender_id :=
SYS.AQ$_AGENT(
name => 'AGT_FILE_ARRIVAL_MONITOR'
,address => NULL
,protocol => NULL);
DBMS_AQ.ENQUEUE(
queue_name => 'SYS.FILE_ARRIVAL_EVENT_Q'
,enqueue_options => enq_opt
,message_properties => msg_prop
,payload => userdata
,msgid => enq_msgid);
COMMIT;
END;
/
-----
-- Listing 2.8: Provide the Scheduler with a list of the
-- initial step startup states for the job chain,
-- then run the chain starting with those step(s)
-----
TRUNCATE TABLE hr.employee_paychecks;
TRUNCATE TABLE hr.payroll_processing_errors;
DECLARE
step_startup_states SYS.SCHEDULER$_STEP_TYPE_LIST;
BEGIN
step_startup_states :=
SYS.SCHEDULER$_STEP_TYPE_LIST(
SYS.SCHEDULER$_STEP_TYPE('file_arrival', 'SUCCEEDED')
,SYS.SCHEDULER$_STEP_TYPE('payroll_validation', 'SUCCEEDED')
,SYS.SCHEDULER$_STEP_TYPE('load_payroll_checks', 'SUCCEEDED')
,SYS.SCHEDULER$_STEP_TYPE('post_load_reporting', 'SUCCEEDED')
,SYS.SCHEDULER$_STEP_TYPE('notify_valid_failure', 'SUCCEEDED')
,SYS.SCHEDULER$_STEP_TYPE('notify_load_failure', 'SUCCEEDED')
);
-- This starts the job chain with all steps set to their
-- failure/success state as specified above ...
DBMS_SCHEDULER.RUN_CHAIN(
chain_name => 'hr.bimonthly_payroll_processing'
,step_state_list => step_startup_states
,job_name => 'hr.mytest'
);
/*
-- ... or just simply start a chain's specified step(s).
DBMS_SCHEDULER.RUN_CHAIN(
chain_name => 'hr.bimonthly_payroll_processing'
,start_steps => 'payroll_validation'
,job_name => 'hr.mytest'
);
*/
END;
/
-----
-- Listing 2.9: Results from unit testing
-----
SET PAGESIZE 50
SET LINESIZE 110
-- Show Job Chain Run results
TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)'
COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A12 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL operation FORMAT A12 HEADING 'Operation'
COL status FORMAT A12 HEADING 'Step|Status'
COL condition FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,operation
,status
,TO_CHAR(additional_info) condition
FROM dba_scheduler_job_log
WHERE owner = 'HR'
AND log_date > (SYSDATE - 1/12)
ORDER BY log_id DESC;
TTITLE OFF
-- Show Job Chain Step details
TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_RUN_DETAILS)'
COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A16 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
FROM dba_scheduler_job_run_details
WHERE owner = 'HR'
AND log_date > (SYSDATE - 1/12)
ORDER BY log_id DESC;
TTITLE OFF
SQL> TTITLE OFF
SQL> -- Show Job Chain Run results
SQL> TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)'
SQL> COL log_id FORMAT 999999 HEADING 'Job|Log#'
SQL> COL job_name FORMAT A12 HEADING 'Job Name'
SQL> COL job_subname FORMAT A20 HEADING 'Step Name'
SQL> COL operation FORMAT A12 HEADING 'Operation'
SQL> COL status FORMAT A12 HEADING 'Step|Status'
SQL> COL condition FORMAT A40 HEADING 'Additional Information' WRAP
SQL> SELECT
2 log_id
3 ,job_name
4 ,job_subname
5 ,operation
6 ,status
7 ,TO_CHAR(additional_info) condition
8 FROM dba_scheduler_job_log
9 WHERE owner = 'HR'
10 AND log_date > (SYSDATE - 1/12)
11 ORDER BY log_id DESC;
>>> Results:
Thu Mar 16 page 1
Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)
Job Step
Log# Job Name Step Name Operation Status Additional Information
------- ------------ -------------------- ------------ ------------ ----------------------------------------
64969 MYTEST CHAIN_RUN SUCCEEDED CHAIN_LOG_ID="64967"
64968 MYTEST POST_LOAD_REPORTING RUN SUCCEEDED CHAIN_LOG_ID="64967", STEP_NAME="POST_LO
AD_REPORTING"
64967 MYTEST CHAIN_START RUNNING
64906 MYTEST CHAIN_RUN SUCCEEDED CHAIN_LOG_ID="64905"
64905 MYTEST CHAIN_START RUNNING
64889 MYTEST CHAIN_RUN FAILED CHAIN_LOG_ID="64865"
64888 MYTEST POST_LOAD_REPORTING RUN STOPPED CHAIN_LOG_ID="64865", STEP_NAME="POST_LOAD_REPORTING", REASON="Stop job with force called by user: 'SYS'"
64887 MYTEST NOTIFY_LOAD_FAILURE_ RUN FAILED CHAIN_LOG_ID="64865",STEP_NAME="NOTIFY_L
0 OAD_FAILURE"
64886 MYTEST LOAD_PAYROLL_CHECKS RUN FAILED CHAIN_LOG_ID="64865", STEP_NAME="LOAD_PA
YROLL_CHECKS"
64885 MYTEST PAYROLL_VALIDATION RUN SUCCEEDED CHAIN_LOG_ID="64865", STEP_NAME="PAYROLL
_VALIDATION"
64865 MYTEST CHAIN_START RUNNING
11 rows selected.
SQL> TTITLE OFF
SQL>
SQL> -- Show Job Chain Step details
SQL> TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_RUN_DETAILS)'
SQL> COL log_id FORMAT 999999 HEADING 'Job|Log#'
SQL> COL job_name FORMAT A16 HEADING 'Job Name'
SQL> COL job_subname FORMAT A20 HEADING 'Step Name'
SQL> COL status FORMAT A12 HEADING 'Step Status'
SQL> COL error# FORMAT 9999999 HEADING 'Error|Code'
SQL> COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SQL> SELECT
2 log_id
3 ,job_name
4 ,job_subname
5 ,status
6 ,error#
7 ,additional_info
8 FROM dba_scheduler_job_run_details JRD
9 WHERE JRD.owner = 'HR'
10 AND log_date > (SYSDATE - 1/12)
11 ORDER BY log_id DESC;
Thu Mar 16 page 1
Results of Job Chain Testing (from DBA_SCHEDULER_JOB_RUN_DETAILS)
Job Error
Log# Job Name Step Name Step Status Code Additional Information
------- ---------------- -------------------- ------------ -------- ----------------------------------------
64969 MYTEST SUCCEEDED 0 CHAIN_LOG_ID="64967"
64968 MYTEST POST_LOAD_REPORTING SUCCEEDED 0 CHAIN_LOG_ID="64967", STEP_NAME="POST_LO
AD_REPORTING"
64906 MYTEST SUCCEEDED 0 CHAIN_LOG_ID="64905"
64889 MYTEST FAILED 27435 CHAIN_LOG_ID="64865"
64888 MYTEST POST_LOAD_REPORTING STOPPED 0 CHAIN_LOG_ID="64865", STEP_NAME="POST_LO
AD_REPORTING", REASON="Stop job with for
ce called by user: 'SYS'"
64887 MYTEST NOTIFY_LOAD_FAILURE_ FAILED 27457 CHAIN_LOG_ID="64865",STEP_NAME="NOTIFY_L
0 OAD_FAILURE", ORA-27457: argument 1 of j
ob "HR.MYTEST" has no value
ORA-20002: ORA-20002: DML errors encount
ered during Employee Payroll Check load
for cycle period 20060316
ORA-06512: at "HR.PAYROLL_PROCESSING", l
ine 113
64886 MYTEST LOAD_PAYROLL_CHECKS FAILED 20002 CHAIN_LOG_ID="64865", STEP_NAME="LOAD_PA
YROLL_CHECKS", ORA-20002: ORA-20002: DML
errors encountered during Employee Payr
oll Check load for cycle period 20060316
ORA-06512: at "HR.PAYROLL_PROCESSING", l
ine 113
64885 MYTEST PAYROLL_VALIDATION SUCCEEDED 0 CHAIN_LOG_ID="64865", STEP_NAME="PAYROLL
_VALIDATION"
8 rows selected.
-----
-- Listing 2.10: New data dictionary views for Job Chains,
-- Chain Steps, and Chain Step Rules
-----
SET PAGESIZE 50
SET LINESIZE 110
-- What Job Chains currently exist?
TTITLE 'Job Chains (from DBA_SCHEDULER_CHAINS)'
COL owner FORMAT A05 HEADING 'Chain|Owner'
COL chain_name FORMAT A30 HEADING 'Chain Name'
COL enabled FORMAT A07 HEADING 'Enabled?'
COL number_of_steps FORMAT 99999 HEADING '# of|Steps'
COL number_of_rules FORMAT 99999 HEADING '# of|Rules'
SELECT
owner
,chain_name
,enabled
,number_of_steps
,number_of_rules
FROM dba_scheduler_chains
;
TTITLE OFF
-- What Job Chain Steps currently exist?
TTITLE 'Job Chain Rules (from DBA_SCHEDULER_CHAIN_STEPS)'
COL owner FORMAT A05 HEADING 'Chain|Owner'
COL chain_name FORMAT A30 HEADING 'Chain Name'
COL step_name FORMAT A20 HEADING 'Step Name' WRAP
COL pgm_or_cond FORMAT A40 HEADING 'Program or Condition' WRAP
SELECT
owner
,chain_name
,step_name
,CASE
WHEN program_name IS NOT NULL
THEN 'PGM: ' || program_owner || '.' || program_name
WHEN event_schedule_owner IS NOT NULL
THEN 'SCD: ' || event_schedule_owner || '.' || event_schedule_name
WHEN event_queue_owner IS NOT NULL
THEN 'QUE: ' || event_queue_owner || '.' || event_queue_name
END AS "pgm_or_cond"
FROM dba_scheduler_chain_steps
;
TTITLE OFF
-- What Job Chain Rules currently exist?
TTITLE 'Job Chain Rules (from DBA_SCHEDULER_CHAIN_RULES)'
COL owner FORMAT A05 HEADING 'Chain|Owner'
COL chain_name FORMAT A30 HEADING 'Chain Name'
COL rule_owner FORMAT A05 HEADING 'Rule|Owner'
COL rule_name FORMAT A09 HEADING 'Rule|Name'
COL condition FORMAT A24 HEADING 'Rule Condition' WRAP
COL action FORMAT A24 HEADING 'Action To Take' WRAP
SELECT
owner
,chain_name
,rule_owner
,rule_name
,condition
,action
FROM dba_scheduler_chain_rules
;
TTITLE OFF
-- Are there any Job Chains running right now?
TTITLE 'Running Job Chains (from DBA_SCHEDULER_RUNNING_CHAINS)'
COL owner FORMAT A05 HEADING 'Chain|Owner'
COL job_name FORMAT A20 HEADING 'Job Name'
COL chain_owner FORMAT A05 HEADING 'Chain|Owner'
COL chain_name FORMAT A30 HEADING 'Chain Name'
COL step_name FORMAT A20 HEADING 'Step Name' WRAP
COL state FORMAT A11 HEADING 'Chain State'
COL error_code FORMAT 99999 HEADING 'Error Code'
SELECT
owner
,job_name
,chain_owner
,chain_name
,step_name
,state
,error_code
FROM dba_scheduler_running_chains
;
TTITLE OFF