該程序腳本最主要的功能實現為通過Oracle自帶的過程包發送郵件來監控ETL的執行情況：
Oracle_SID=orcl
ORACLE_BASE=/opt/Oracle
ORACLE_HOME=/opt/Oracle/product/10.2.0
export ORACLE_SID ORACLE_BASE Oracle_HOME
PWD_DIR=/home/Oracle/shell
SQLPLUS=${Oracle_HOME}/bin/sqlplus
CONFIG_INI=${PWD_DIR}/ini/config.ini
while read gameuser
do
echo ${gameuser}
echo ${SQLPLUS}
cd ${PWD_DIR}
${SQLPLUS} ${gameuser} << !
@etl_monitor.sql;
/
exit;
!
done<${CONFIG_INI}
etl_monitor.sql腳本為：
DECLARE
p_txt VARCHAR2 (4000);
p_txt_all VARCHAR2 (4000);
BEGIN
FOR r IN (SELECT job_name,
run_cnt,
table_name,
column_name
FROM etl_monitor_config_tab)
LOOP
-- Call the Etl Monitor function
p_txt :=
etl_monitor (r.job_name,
r.run_cnt,
r.table_name,
r.column_name);
p_txt_all := p_txt_all || CHR (13) || p_txt;
END LOOP;
-- Call the Send Mail function
procsendemail (p_txt_all,
'Etl Moniotr',
'[email protected]',
'[email protected]',
'mail.kingsoft.com',
25,
1,
'xxxxxx',
'xxxxxx',
'',
'bit 7');
p_txt_all := '';
END;
create or replace function etl_monitor(job_name varchar2,
run_cnt int,
table_name varchar2,
column_name varchar2)
RETURN varchar2 IS
v_monitor_date date; --The monitor of the proc's date
v_job_name varchar2(130);
v_log_id number;
v_result1 char(1); --The status of the proc's result1
v_result2 char(1); --The status of the proc's result2
v_status_cnt int;
v_record_num int; --The number of the job run
v_result varchar2(4000);
v_sql varchar2(1000);
begin
v_monitor_date := trunc(sysdate);
v_job_name := job_name;
v_result1 := '0';
v_result2 := '0';
v_sql := 'select count(1) from ';
if run_cnt = 1 then
select log_id
into v_log_id
from user_scheduler_job_run_details
where job_name = v_job_name
and trunc(actual_start_date) = v_monitor_date;
else
select max(log_id)
into v_log_id
from user_scheduler_job_run_details
where job_name = v_job_name
and trunc(actual_start_date) = v_monitor_date;
end if;
select count(*)
into v_status_cnt
from user_scheduler_job_run_details
where log_id = v_log_id
and status = 'SUCCEEDED';
if v_status_cnt = 0 then
goto error1;
end if;
v_result1 := '1';
v_sql := v_sql || table_name || ' ' || 'where trunc(' || column_name ||
') =' || 'trunc(sysdate-1) and rownum=1';
execute immediate v_sql
into v_record_num;
if v_record_num > 0 then
v_result2 := '1';
else
v_status_cnt := 0;
goto error1;
end if;
if v_result1 = '1' and v_result2 = '1' then
v_result := SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '.' ||
v_job_name || ' At ' || v_monitor_date || ' IS SUCCEEDED';
end if;
<<error1>>
if v_status_cnt = 0 then
select OWNER || '.' || JOB_NAME || ' At ' || TRUNC(ACTUAL_START_DATE) ||
'IS ' ADDITIONAL_INFO
into v_result
from user_scheduler_job_run_details
where log_id = v_log_id;
end if;
return v_result;
exception
when others then
return SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '.' || v_job_name || ' At ' || v_monitor_date || ' IS NOT EXECUTE';
end;
