etl_monitor_v2.sh //控制何时发短信
----------------------------------
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_v2.ini
gameuser='GAMEDATA/"h^zx@xxnm(Ybn"'
echo ${gameuser}
echo ${SQLPLUS}
cd ${PWD_DIR}
${SQLPLUS} ${gameuser} << !
@etl_monitor_v2.sql;
/
exit;
!
--------------------------------------
etl_monitor_v2.sql //真正发短信的东东
declare
p_txt varchar2(4000);
p_txt_all varchar2(4000);
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
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_v2(r.job_name, r.run_cnt);
p_txt_all := p_txt_all || p_txt; --短信的内容!
end loop;
if p_txt_all is not null then
req := UTL_HTTP.BEGIN_REQUEST('http://192.168.XXX/pass/web/alertsms.php?data=' ||
p_txt_all);
resp := UTL_HTTP.GET_RESPONSE(req);
utl_http.end_response(resp);
end if;
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(resp);
end;
--PS:其实http://192.168.XXX/pass/web/alertsms.php这个链接地址是真正的发短信的接口
这个具体是通过PHP来实现的
-------------------------------------
etl_monitor_v2 的内容
create or replace function etl_monitor_v2(job_name varchar2, run_cnt int)
RETURN varchar2 IS
v_monitor_date date; --The monitor of the proc's date
v_job_name varchar2(130);
v_log_id number;
v_status_cnt int;
v_result varchar2(4000);
begin
v_monitor_date := trunc(sysdate);
v_job_name := job_name;
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
select OWNER || '.' || JOB_NAME || '+FALSED+' ||
TO_CHAR(TRUNC(ACTUAL_START_DATE), 'MM-DD')
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 || '+NOEXECUTE+' || TO_CHAR(v_monitor_date,
'MM-DD');
end;