ORACLE Automatic Email code

The most important function of this program script is to monitor the performance of the ETL by sending mail via Oracle's own process package:
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
Todo
Echo ${gameuser}
Echo ${sqlplus}
CD ${pwd_dir}
${sqlplus} ${gameuser} <@etl_monitor. sql;
/
Exit
!
Done<${config_ini}
The Etl_monitor.sql script is:
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 () 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 ' are not EXECUTE ';
End 1 2 3 4 5 Next > Full text reading tips: Try the "←→" button, turn the page more convenient Oh!