oracle 调用链接接口发送短消息

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('' ||

                                   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:其实这个链接地址是真正的发短信的接口

这个具体是通过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;