oracle 发邮件编码,ORACLE 自动发邮件代码

您可能感兴趣的话题：

Oracle

核心提示：该程序脚本最主要的功能实现为通过oracle自带的过程包发送邮件来监控ETL的执行情况

该程序脚本最主要的功能实现为通过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

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;

<>

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;