Ora | HTML eMail Util

HTML eMail Utility

This Package/function is useful for sending emails from your pl/sql Code. It send out emails with simple HMTL tags, which can used for neatly formatting your emails. 

Download the Source Code

Package_spec - PKG_HTML_eMAIL_spec.sql
Package_body -PKG_HTML_eMAIL_body.sql

Installation Steps

1. Download the package spec and the body from the above links.
2. Run the downloaded script and ensure you have a valid PKG_HTML_eMAIL package.

Usage Notes

****************************
* Example-1 : Simple Email
****************************
DECLARE
  a NUMBER;

BEGIN
  a := PKG_HTML_eMAIL.sendSimpleEmail('test@domain.com', 
                                       'testing', 
            '

Testing HTML body

             


                    
R1 - C1 
                    
R1 - C2 
                    
R1 - C3 
                 

                    
R2 - C1 
                    
R2 - C2 
                    
R2 - C3 
                 

');
  
  IF a != 0 THEN
     dbms_output.put_line('ERROR_TEXT:'||PKG_HTML_eMAIL.getErrorText);
     --then error
  END IF;
END;
*/

/******************************************
 * Example-2 : eMail with a table content
 ******************************************
DECLARE
  conn NUMBER;
  a NUMBER;

BEGIN
  conn := PKG_HTML_eMAIL.getConnection;
  PKG_HTML_eMAIL.setHeaders(p_conn_id=> conn,
                           p_to_emailIDs => 'test@domain.com,test2@domain.com',
                           p_cc_eMailIDs => null,
                           p_subject => 'testing customization');

  PKG_HTML_eMAIL.SetTableBody( conn, 'TABLE-Header', 'H', null);
  PKG_HTML_eMAIL.SetTableBody( conn, 'r1-c1~r1-c2~r1-c3', 'R', null);
  PKG_HTML_eMAIL.SetTableBody( conn, '~TOTAL~:10000', 'SH', null);
  PKG_HTML_eMAIL.SetTableBody( conn, 'r2-c1~r2-c2~r2-c3', '', null);
  PKG_HTML_eMAIL.SetTableBody( conn, 'TOTAL', 'SH', null);

  a := PKG_HTML_eMAIL.sendeMail(conn);

  IF a != 0 THEN
     dbms_output.put_line( 'ERROR_TEXT:'||PKG_HTML_eMAIL.getErrorText);
     -- then error
  END IF;
END;
*/

/********************************
 * Example-3 : Customized Email
 ********************************
DECLARE
  conn  NUMBER;
  a     NUMBER;
BEGIN

  conn := PKG_HTML_eMAIL.getConnection;
  PKG_HTML_eMAIL.setHeaders(p_conn_id=> conn,
                       p_to_emailIDs => 'test@domain.com,test2@domain.com',
                       p_cc_eMailIDs => null,
                       p_subject => 'testing customization');

  --<<<<<<<< customize the body title
  PKG_HTML_eMAIL.setBodyTitle( conn, '

 this is the BODY title 
');   
  --you can add your own CSS
  PKG_HTML_eMAIL.setCSS( conn, '.TblReportsHead4{background-color:red;}');   

  --customize the alignment of table cells 
  PKG_HTML_eMAIL.setTableCellAlignment(conn, 'L~R~C~');  

  --'H' for header
  PKG_HTML_eMAIL.SetTableBody( conn, 'TABLE-Header', 'H', null);
  --'R' for record   
  PKG_HTML_eMAIL.SetTableBody( conn, 'r1-c1~r1-c2~r1-c3', 'R', null); 
  --'SH' for Sub-Header
  PKG_HTML_eMAIL.SetTableBody( conn, '~TOTAL~:10000', 'SH', null);    
  PKG_HTML_eMAIL.SetTableBody( conn, 'r2-c1~r2-c2~r2-c3', '', null);
  -- use the CUSTOMIZED CSS here
  PKG_HTML_eMAIL.SetTableBody( conn, 'TOTAL', 'SH', 'TblReportsHead4');      

  a := PKG_HTML_eMAIL.sendeMail(conn);

  IF a != 0 THEN
     dbms_output.put_line( 'ERROR_TEXT:'||PKG_HTML_eMAIL.getErrorText);
     -- then error
  END IF;
END;