ORACLE Automatic Email code

Source: Internet
Author: User
Tags date count end goto ini sql return sqlplus

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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.