网学网为需要ORACLE的朋友们搜集整理了ORACLE 自动发邮件代码相关资料，希望对各位网友有所帮助!
该程序脚本最主要的功能实现为通过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'',
''xxx@163.com'',
''xxxx@kingsoft.com'',
''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;
