并发失败后接收电子邮件的最佳方法(SQL-触发器-应用程序)

问题描述

如果并发执行最终出现错误，我想收到一封电子邮件；

I want to receive an email if the concurrent execution endded up with an error;

看看:我有一个查询，其中包含并发的最新执行(我正在使用Oracle 11g):

Take a look: i have a query which contains the latest execution of a concurrent (i am using Oracle 11g):

select *
  from
  (
    select yer.user_murcurrent_program_yere program,
           mur.request_id "request id",
           mur.status_code status,
           row_number() over (partition by yer.user_murcurrent_program_yere order by mur.request_id desc ) as rn
      from fnd_murcurrent_programs_tl yer
      join fnd_murcurrent_requests mur
        on mur.murcurrent_program_id=yer.murcurrent_program_id
      join fnd_user us
        on mur.requested_by = us.user_id
     where mur.actual_start_date >= date'2019-11-20' 
       and mur.actual_start_date <  date'2019-11-23' + 1
       and (yer.user_murcurrent_program_yere like 'Report sales')   
   )                                         
where rn = 1;

此查询返回如下内容:

|   program  |request_id|status |
|Report sales|5878547894|WARNING|

在这种情况下，我想收到一封电子邮件，内容是:

In this case i would like to recieve an email saying:

以下是可能的状态:

我只想接收状态为:警告，错误或待机的电子邮件.

我该怎么做?我应该做什么或创建什么应用程序?我需要这样做，我很迷路.

How can i do that? What application should i do or create?. I need to do that and i'm pretty lost.

能请你帮我吗?

推荐答案

您可以将双管道(||)用作串联运算符，并按IN运算符后括号中列出的所需状态类型过滤结果用于查询.

You can use double pipes(||) as concatenation operators, and filter the results out by your desired status types listed within parentheses after the IN operator for the query.

创建一个过程，并将查询作为游标进入该过程，并使用 utl_http 程序包，如下所示:

Create a procedure and take your query into it as a cursor and use utl_http package within that procedure as below :

create or replace procedure pr_mail_me is

  v_email varchar2(100) := '[email protected]';
  v_rep   varchar2(4000);
  v_url   varchar2(4000);

  cursor crs_request is
  select 'The concurrent '||program||' with request_id '||request_id||' ended with status '|| 
           status as message, request_id
    from
    (
       <the subquery>
      )
   where rn = 1
     and status in ('WARNING','ERROR','STAND BY');    

begin

  for c in crs_request
  loop
  begin

    v_url := 'http://www.mycompany.com/path_to/default.aspx?email=' ||
              v_email ||'&out_message='||c.message||'&out_request_id='||c.request_id;
    v_rep := utl_http.request(utl_url.escape(v_url,false,'UTF-8'));

   exception
       when others then
            v_url := 'http://www.mycompany.com/path_to/default.aspx?email=' ||
                      v_email ||'&out_message='||substr(sqlerrm,1,250)||'&out_request_id='||c.request_id;
                      v_rep := utl_http.request(utl_url.escape(v_url,false,'UTF-8'));
        end;
  end loop;
end;

以便在调用此过程时接收电子邮件.

in order to receive e-mails as calling this procedure.

这篇关于并发失败后接收电子邮件的最佳方法(SQL-触发器-应用程序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！