email - liamlamth/blog GitHub Wiki

  • reference: https://oracle-base.com/articles/misc/email-from-oracle-plsql

  • required ACL

    ### check before action
select * from dba_network_acls;                                                                         ### the existing host and its assigned xml
select * from dba_network_acl_privileges;                                                               ### the xml and its assigned schema(s)

### add priviledge if a host is already assigned to any xml
begin
  dbms_network_acl_admin.add_privilege (
    acl         => 'acl_file.xml',
    principal   => 'email_user',
    is_grant    => true,
    privilege   => 'connect',
    position    => null,
    start_date  => null,
    end_date    => null);
  commit;
end;
/ 

### if the ACL xml hasn't been created
### if try to assign a assigned host to another xml, it will replace the original. be aware.
dbms_network_acl_admin.create_acl
dbms_network_acl_admin.assign_acl

  • required package

    create or replace package string_api as
   type t_split_array is table of varchar2(4000);
   function split_text (p_text       in  clob,
                        p_delimeter  in  varchar2 default ',')
    return t_split_array;
   procedure print_clob (p_clob  in  clob);
   procedure print_clob_old (p_clob  in  clob);
   procedure print_clob_htp (p_clob  in  clob);
   procedure print_clob_htp_old (p_clob  in  clob);
   function get_uri_paramter_value (p_uri         in  varchar2,
                                    p_param_name  in  varchar2)
    return varchar2;
end string_api;
/

create or replace package body string_api as
type t_uri_array is table of varchar2(32767) index by varchar2(32767);
g_last_uri varchar2(32767) := 'initialized';
g_uri_tab  t_uri_array;
------------------------------------------------------------------------------
    function split_text (p_text       in  clob,
                         p_delimeter  in  varchar2 default ',')
      return t_split_array is
    -- Could be replaced by APEX_UTIL.STRING_TO_TABLE.
      l_array  t_split_array   := t_split_array();
      l_text   clob := p_text;
      l_idx    number;
    begin
      l_array.delete;
   
      if l_text is null then
        raise_application_error(-20000, 'P_TEXT parameter cannot be NULL');
      end if;
    
      while l_text is not null loop
        l_idx := instr(l_text, p_delimeter);
         l_array.extend;
        if l_idx > 0 then
          l_array(l_array.last) := substr(l_text, 1, l_idx - 1);
          l_text := substr(l_text, l_idx + 1);
        else
          l_array(l_array.last) := l_text;
          l_text := null;
        end if;
      end loop;
      return l_array;
    end split_text;
    -------------------------------------------------------------------------------
    procedure print_clob (p_clob in clob) is
      l_offset number := 1;
      l_chunk  number := 255;
    begin
      loop
        exit when l_offset > length(p_clob);
        dbms_output.put_line(substr(p_clob, l_offset, l_chunk));
        l_offset := l_offset + l_chunk;
      end loop;
    end print_clob;
    -------------------------------------------------------------------------------
    procedure print_clob_old (p_clob in clob) is
      l_offset number := 1;
      l_chunk  number := 255;
    begin
      loop
        exit when l_offset > dbms_lob.getlength(p_clob);
        dbms_output.put_line(dbms_lob.substr(p_clob, l_offset, l_chunk));
        l_offset := l_offset + l_chunk;
      end loop;
    end print_clob_old;
    -------------------------------------------------------------------------------
   procedure print_clob_htp (p_clob in clob) is
      l_offset number := 1;
      l_chunk  number := 3000;
    begin
      loop
        exit when l_offset > length(p_clob);
        htp.prn(substr(p_clob, l_offset, l_chunk));
        l_offset := l_offset + l_chunk;
      end loop;
    end print_clob_htp;
    -------------------------------------------------------------------------------
    procedure print_clob_htp_old (p_clob in clob) is
      l_offset number := 1;
      l_chunk  number := 3000;
    begin
      loop
         exit when l_offset > dbms_lob.getlength(p_clob);
        htp.prn(dbms_lob.substr(p_clob, l_offset, l_chunk));
        l_offset := l_offset + l_chunk;
      end loop;
    end print_clob_htp_old;
    -------------------------------------------------------------------------------
    function get_uri_paramter_value (p_uri         in  varchar2,
                                     p_param_name  in  varchar2)
      return varchar2 is
    -------------------------------------------------------------------------------
    -- Example:
    -- l_uri := 'https://localhost:8080/my_page.php?param1=value1¶m2=value2¶m3=value3';
    -- l_value := string_api.get_uri_paramter_value(l_uri, 'param1')
    -------------------------------------------------------------------------------
      l_uri    varchar2(32767);
      l_array  t_split_array   := t_split_array();
      l_idx    number;
    begin
      if p_uri is null or p_param_name is null then
        raise_application_error(-20000, 'p_uri and p_param_name must be specified.');
      end if;
      
      if p_uri != g_last_uri then
        -- First time we've seen this URI, so build the key-value table.
        g_uri_tab.delete;
        g_last_uri := p_uri;
        l_uri      := translate(g_last_uri, '&?', '^^');
        l_array    := split_text(l_uri, '^');
        for i in 1 .. l_array.count loop
          l_idx := instr(l_array(i), '=');
          if l_idx != 0 then
            g_uri_tab(substr(l_array(i), 1, l_idx - 1)) := substr(l_array(i), l_idx + 1);
          end if;
        end loop;
      end if;
      
      return g_uri_tab(p_param_name);
    exception
      when no_data_found then
        return null;
     end get_uri_paramter_value;
end string_api;
/

  • required procedure

    create or replace procedure send_mail (p_to          in varchar2,
                                       p_cc          in varchar2 default null,
                                       p_bcc         in varchar2 default null,
                                       p_subject     in varchar2,
                                       p_html_msg    in varchar2 default null,
                                       p_attach_name in varchar2 default null,
                                       p_attach_mime in varchar2 default null,
                                       p_attach_blob in blob default null,
                                       p_send_time   in varchar2 default null,
                                       p_from        in varchar2 default '[email protected]',
                                       p_smtp_host   in varchar2 default 'smtphost.domain.local',
                                       p_smtp_port   in number default 25)
as
  l_mail_conn   utl_smtp.connection;
  l_boundary    varchar2(50) := '----=*#abc1234321cba#*=';
  l_step        pls_integer := 57;
  l_send_time   varchar2(100);
  
  procedure process_recipients(p_mail_conn in out utl_smtp.connection,
                               p_list      in     varchar2)
   as
    l_tab string_api.t_split_array;
  begin
    if trim(p_list) is not null then
      l_tab := string_api.split_text(p_list);
      for i in 1 .. l_tab.count loop
        utl_smtp.rcpt(p_mail_conn, trim(l_tab(i)));
      end loop;
    end if;
  end;
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);
  process_recipients(l_mail_conn, p_to);
  process_recipients(l_mail_conn, p_cc);
  process_recipients(l_mail_conn, p_bcc);

  utl_smtp.open_data(l_mail_conn);
  
  if p_send_time is not null then
    l_send_time := to_char( to_timestamp_tz(p_send_time,'YYYY-MM-DD'), 'Dy, DD Mon YYYY HH24:MI:SS TZHTZM','NLS_DATE_LANGUAGE=ENGLISH');
  else
    l_send_time := to_char( systimestamp, 'Dy, DD Mon YYYY HH24:MI:SS TZHTZM','NLS_DATE_LANGUAGE=ENGLISH');
  end if;
  utl_smtp.write_data(l_mail_conn, 'Date: ' || l_send_time || utl_tcp.crlf);

  utl_smtp.write_data(l_mail_conn, 'To: ' || p_to || utl_tcp.crlf);
  if trim(p_cc) is not null then
    utl_smtp.write_data(l_mail_conn, 'CC: ' || replace(p_cc, ',', ';') || utl_tcp.crlf);
  end if;
  if trim(p_bcc) is not null then
    utl_smtp.write_data(l_mail_conn, 'BCC: ' || replace(p_bcc, ',', ';') || utl_tcp.crlf);
  end if;
  utl_smtp.write_data(l_mail_conn, 'From: ' || p_from || utl_tcp.crlf);
  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Subject: '|| replace(utl_encode.mimeheader_encode(p_subject, 'utf8'),chr(13)||chr(10),'') || 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);
  utl_smtp.write_data(l_mail_conn, '--' || l_boundary || utl_tcp.crlf);
  utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html; charset="utf-8"' || utl_tcp.crlf || utl_tcp.crlf);
  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_html_msg));
  utl_smtp.write_data(l_mail_conn, utl_tcp.crlf || utl_tcp.crlf);

  if p_attach_name is not null then
    utl_smtp.write_data(l_mail_conn, '--' || l_boundary || utl_tcp.crlf);
    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"; charset="utf-8"' || utl_tcp.crlf));
    utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf);
    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="' || p_attach_name || '"' || utl_tcp.crlf || utl_tcp.crlf));
    for i in 0 .. trunc((dbms_lob.getlength(p_attach_blob) - 1 )/l_step) loop
      utl_smtp.write_data(l_mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(p_attach_blob, l_step, i * l_step + 1))) || utl_tcp.crlf);
    end loop;
    utl_smtp.write_data(l_mail_conn, 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;
/

  • usage

    declare
  v_blob blob;
begin
  send_mail('[email protected]','[email protected],[email protected]',null,             ### delimiter for multiple recipients must be ","
            'Test Message','<html><p>This is a test message.</p></html>', 
            'test.png','image/png',v_blob,                                                    ### (optional) attachment
            '2022-01-01'                                                                      ### (optional) fake send time
            )  
end;
/
⚠️ **GitHub.com Fallback** ⚠️