当前位置： 文江博客话题详情

如何捕获错误并存储到错误表| pl/sql |

发布于 2025-02-08 11:16:48 字数 5038 浏览 2 评论 0原文

我一直在编写一个代码来实现这一目标，但是完全

需要您为这种情况提供帮助，

我已经使用oracle Apex创建了一个带有Interactive Report的应用程序，并在我的页面

“

/strong>

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_html_msg  IN VARCHAR2 DEFAULT NULL,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
    
  IF p_html_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_html_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/

在发送邮件按钮上单击我的下面代码完美运行

declare 
    l_context apex_exec.t_context;    
    l_emailsidx  pls_integer;
    l_namesids    pls_integer;
    l_region_id number;
    l_html VARCHAR2(32767);
     
begin


    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = 1
       and static_id      = 'CUSTOMERS';
 
    l_context := apex_region.open_query_context (
                        p_page_id => 1,
                        p_region_id => l_region_id );

    -- Get the column positions for EMAIL and NAME columns
    l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
    l_namesids := apex_exec.get_column_position( l_context, 'NAME' );
    
    while apex_exec.next_row( l_context ) loop  
      
        send_mail(
            p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
            p_from      => '[email protected]',
            p_subject   => :P2_SUBJECT,
            p_html_msg  => :P3_HTML,
            p_smtp_host => 'smtp.mycompany.com');

Exception 
      when OTHERS THEN 
             // log my error to ERROR table     
             CONTINUE;

     end loop;
 
     apex_exec.close( l_context );
exception
     when others then
         apex_exec.close( l_context );
     raise; 
end;

，如果任何电子邮件ID无效，并且我的UTL SMTP无法发送邮件，则会引起错误，我想捕获该错误 - ＆gt;将我的以下错误表存储到带有电子邮件ID

create table employee_error(
emailid clob,
error_msg clob
);

第一个代码

Exception 
      when OTHERS THEN 
             Insert into  employee_error  values(p_to,sqlerr);
             CONTINUE;

第二代码尝试：我也尝试了一个代码：ora-06550

while apex_exec.next_row( l_context ) loop  
   begin
        send_mail(
            p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
            p_from      => '[email protected]',
            p_subject   => :P2_SUBJECT,
            p_html_msg  => :P3_HTML,
            p_smtp_host => 'smtp.mycompany.com');

   Exception 
      when OTHERS THEN 
        Insert into employee_error (emailid, error_msg)
          values
          (apex_exec.get_varchar2( l_context, l_emailsidx ), sqlerrm);
   end;
end loop;

当我尝试使用第二个代码时，我的错误

ORA-06550: column not allowed , SQL statement ignored

即使发生错误，我的发送邮件的处理也不应停止 我添加了继续的例外。

，以将错误表记录到错误表，然后继续选择下一个ID触发邮件

原文

I have been writing a code to achieve this but fully stuck

Need you helping hands for this scenario

I have created a application with Oracle APEX with interactive report and form in my page

MY GUI

My procedure to sent mail

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_html_msg  IN VARCHAR2 DEFAULT NULL,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
    
  IF p_html_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_html_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/

On send mail button click my below code run perfectly

declare 
    l_context apex_exec.t_context;    
    l_emailsidx  pls_integer;
    l_namesids    pls_integer;
    l_region_id number;
    l_html VARCHAR2(32767);
     
begin


    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = 1
       and static_id      = 'CUSTOMERS';
 
    l_context := apex_region.open_query_context (
                        p_page_id => 1,
                        p_region_id => l_region_id );

    -- Get the column positions for EMAIL and NAME columns
    l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
    l_namesids := apex_exec.get_column_position( l_context, 'NAME' );
    
    while apex_exec.next_row( l_context ) loop  
      
        send_mail(
            p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
            p_from      => '[email protected]',
            p_subject   => :P2_SUBJECT,
            p_html_msg  => :P3_HTML,
            p_smtp_host => 'smtp.mycompany.com');

Exception 
      when OTHERS THEN 
             // log my error to ERROR table     
             CONTINUE;

     end loop;
 
     apex_exec.close( l_context );
exception
     when others then
         apex_exec.close( l_context );
     raise; 
end;

But if any email id is invalid and my UTL SMTP not able to send mail it throws an error and I want to catch that error -> store to my below error table with email id

create table employee_error(
emailid clob,
error_msg clob
);

First code try

Exception 
      when OTHERS THEN 
             Insert into  employee_error  values(p_to,sqlerr);
             CONTINUE;

Second code try : I tried this one also but it thrown me error : ORA-06550

while apex_exec.next_row( l_context ) loop  
   begin
        send_mail(
            p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
            p_from      => '[email protected]',
            p_subject   => :P2_SUBJECT,
            p_html_msg  => :P3_HTML,
            p_smtp_host => 'smtp.mycompany.com');

   Exception 
      when OTHERS THEN 
        Insert into employee_error (emailid, error_msg)
          values
          (apex_exec.get_varchar2( l_context, l_emailsidx ), sqlerrm);
   end;
end loop;

When I tried with second code I got below error

ORA-06550: column not allowed , SQL statement ignored

My processing of sending mail should not stop even if error occur so
I added continue to my exception.

So that log the error to error table and continue picking next id to trigger mail

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

月野兔 2025-02-15 11:16:48

您非常接近 - 只有嵌入另一个开始 - except-end-end块进入循环。这样的事情：

while apex_exec.next_row( l_context ) loop  
   begin
        send_mail(
            p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
            p_from      => '[email protected]',
            p_subject   => :P2_SUBJECT,
            p_html_msg  => :P3_HTML,
            p_smtp_host => 'smtp.mycompany.com');

   Exception 
      when OTHERS THEN 
        Insert into employee_error (emailid, error_msg)
          values
          (apex_exec.get_varchar2( l_context, l_emailsidx ), sqlerrm);
   end;
end loop;

You're quite close - just embed another BEGIN-EXCEPTION-END block into the loop. Something like this:

while apex_exec.next_row( l_context ) loop  
   begin
        send_mail(
            p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
            p_from      => '[email protected]',
            p_subject   => :P2_SUBJECT,
            p_html_msg  => :P3_HTML,
            p_smtp_host => 'smtp.mycompany.com');

   Exception 
      when OTHERS THEN 
        Insert into employee_error (emailid, error_msg)
          values
          (apex_exec.get_varchar2( l_context, l_emailsidx ), sqlerrm);
   end;
end loop;
回复 原文
弥繁 2025-02-15 11:16:48

我这样做是通过从作者@littlefoot的参考来完成的，它起作用了

declare 
    l_context apex_exec.t_context;    
    l_emailsidx  pls_integer;
    l_namesids    pls_integer;
    l_region_id number;
    l_html VARCHAR2(32767);
    v_errm varchar2(4000 char);
    v_email  varchar2(4000 char);
     
begin


    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = 1
       and static_id      = 'CUSTOMERS';
 
    l_context := apex_region.open_query_context (
                        p_page_id => 1,
                        p_region_id => l_region_id );

    -- Get the column positions for EMAIL and NAME columns
    l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
    l_namesids := apex_exec.get_column_position( l_context, 'NAME' );
    
    while apex_exec.next_row( l_context ) loop  
       begin
            send_mail(
                p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
                p_from      => '[email protected]',
                p_subject   => :P2_SUBJECT,
                p_html_msg  => :P3_HTML,
                p_smtp_host => 'smtp.mycompany.com');
    
       Exception 
          when OTHERS THEN 
            v_errm  := sqlerrm;
            v_email := apex_exec.get_varchar2( l_context, l_emailsidx );
            Insert into employee_error values (v_errm ,v_email);
       end;
    end loop;

 
     apex_exec.close( l_context );
exception
     when others then
         apex_exec.close( l_context );
     raise; 
end;

I done like this by taking reference from author @littlefoot and it worked

declare 
    l_context apex_exec.t_context;    
    l_emailsidx  pls_integer;
    l_namesids    pls_integer;
    l_region_id number;
    l_html VARCHAR2(32767);
    v_errm varchar2(4000 char);
    v_email  varchar2(4000 char);
     
begin


    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = 1
       and static_id      = 'CUSTOMERS';
 
    l_context := apex_region.open_query_context (
                        p_page_id => 1,
                        p_region_id => l_region_id );

    -- Get the column positions for EMAIL and NAME columns
    l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
    l_namesids := apex_exec.get_column_position( l_context, 'NAME' );
    
    while apex_exec.next_row( l_context ) loop  
       begin
            send_mail(
                p_to        => apex_exec.get_varchar2( l_context, l_emailsidx ),
                p_from      => '[email protected]',
                p_subject   => :P2_SUBJECT,
                p_html_msg  => :P3_HTML,
                p_smtp_host => 'smtp.mycompany.com');
    
       Exception 
          when OTHERS THEN 
            v_errm  := sqlerrm;
            v_email := apex_exec.get_varchar2( l_context, l_emailsidx );
            Insert into employee_error values (v_errm ,v_email);
       end;
    end loop;

 
     apex_exec.close( l_context );
exception
     when others then
         apex_exec.close( l_context );
     raise; 
end;
回复 原文
我要还你自由 2025-02-15 11:16:48

您需要使用自主交易来记录功能。它不会中断您的流程逻辑，并且在回滚时会保持记录的条目。

 创建table log_table（
  DTTM时间戳默认Systimestamp，
  err_code int，
  err_message varchar2（1000）
）
 
 创建表T（
  DTTM时间戳默认Systimestamp，
  Val Int
）
 
 创建过程log_error（
  代码int，
  MSG VARCHAR2
）
作为
  pragma automous_transaction;
开始
  插入log_table（dttm，err_code，err_message）
  值（Systimestamp，Code，MSG）;
  犯罪;
结尾;
/
 
 创建过程测试（
  p_iter int，
  p_err_stop int默认null
）
作为
  err_cnt int：= 0;
开始
  对于我1..p_iter循环
    开始
      如果mod（i，2）= 0，则        
        err_cnt：= err_cnt + 1;

        rish_application_error（
          -20001，
          我
        ）； 
      如果结束；
      插入t（val）
      值（i）;
      
      例外
        那时其他人
          log_error（sqlcode，sqlerrm）;
          如果err_cnt＆gt;然后
            /*停止执行*/
            rish_application_error（-20002，'太多错误'）;
          如果结束；
    结尾;
  结束循环；
结尾;
/
 
 开始
  测试（10）;
  犯罪;
  测试（10，2）;
  犯罪;
结尾;/
 
 ORA-2002002：错误太多

ora-06512：在“ fiddle_tbtpmhpkcneeukuqefow.test”，第26行
ORA-06512：在第4行

 选择 *
来自log_table
 
 dttm | err_code | err_message  
：--------------------------------- | -------：| ：-----------------
2022-06-19 11：15：49.761825 | -20001 | ORA-20001：2 
2022-06-19 11：15：49.762820 | -20001 | ORA-20001：4 
2022-06-19 11：15：49.763268 | -20001 | ORA-20001：6 
2022-06-19 11：15：49.763810 | -20001 | ORA-20001：8 
2022-06-19 11：15：49.764210 | -20001 | ORA-20001：10
2022-06-19 11：15：49.765069 | -20001 | ORA-20001：2 
2022-06-19 11：15：49.765624 | -20001 | ORA-20001：4 
2022-06-19 11：15：49.766282 | -20001 | ORA-20001：6 

 选择 *
从t
 
 dttm |瓦尔
：---------------------------------- | - ：
2022-06-19 11：15：49.754656 | 1
2022-06-19 11：15：49.762612 | 3
2022-06-19 11：15：49.763093 | 5
2022-06-19 11：15：49.763500 | 7
2022-06-19 11：15：49.764034 | 9

db＆lt;＆gt; fiddle

You need to use autonomous transactions for logging capabilities. It will not interrupt your flow logic and will keep logged entries in case of rollback.

create table log_table (
  dttm timestamp default systimestamp,
  err_code int,
  err_message varchar2(1000)
)

create table t (
  dttm timestamp default systimestamp,
  val int
)

create procedure log_error (
  code int,
  msg varchar2
)
as
  pragma autonomous_transaction;
begin
  insert into log_table (dttm, err_code, err_message)
  values(systimestamp, code, msg);
  commit;
end;
/

create procedure test (
  p_iter int,
  p_err_stop int default null
)
as
  err_cnt int := 0;
begin
  for i in 1..p_iter loop
    begin
      if mod(i, 2) = 0 then        
        err_cnt := err_cnt + 1;

        raise_application_error(
          -20001,
          i
        ); 
      end if;
      insert into t (val)
      values (i);
      
      exception
        when others then
          log_error(sqlcode, sqlerrm);
          if err_cnt > p_err_stop then
            /*Stop execution*/
            raise_application_error(-20002, 'Too many errors');
          end if;
    end;
  end loop;
end;
/

begin
  test(10);
  commit;
  test(10, 2);
  commit;
end;/

ORA-20002: Too many errors

ORA-06512: at "FIDDLE_TBTPMHPKCNYEUKUQEFOW.TEST", line 26 ORA-06512: at line 4

select *
from log_table

DTTM                       | ERR_CODE | ERR_MESSAGE  
:------------------------- | -------: | :------------
2022-06-19 11:15:49.761825 |   -20001 | ORA-20001: 2 
2022-06-19 11:15:49.762820 |   -20001 | ORA-20001: 4 
2022-06-19 11:15:49.763268 |   -20001 | ORA-20001: 6 
2022-06-19 11:15:49.763810 |   -20001 | ORA-20001: 8 
2022-06-19 11:15:49.764210 |   -20001 | ORA-20001: 10
2022-06-19 11:15:49.765069 |   -20001 | ORA-20001: 2 
2022-06-19 11:15:49.765624 |   -20001 | ORA-20001: 4 
2022-06-19 11:15:49.766282 |   -20001 | ORA-20001: 6 

select *
from t

DTTM                       | VAL
:------------------------- | --:
2022-06-19 11:15:49.754656 |   1
2022-06-19 11:15:49.762612 |   3
2022-06-19 11:15:49.763093 |   5
2022-06-19 11:15:49.763500 |   7
2022-06-19 11:15:49.764034 |   9

db<>fiddle here

回复 原文
~没有更多了~

关于作者

究竟谁懂我的在乎

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

十二

文章 0 评论 0

飞烟轻若梦

文章 0 评论 0

OPleyuhuo

文章 0 评论 0

wxb0109

文章 0 评论 0

旧城空念

文章 0 评论 0

-小熊_

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文