How to catch error and store to Error table | PL/SQL |

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

Solution

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;