How to catch error and store to Error table

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

CodePudding user response：

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;

CodePudding user response：

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;

CodePudding user response：

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