Skip to Main Content
  • Questions

  • Sending Mail Through Oracle Which Has HTML Table Content Or Sending mail Through Attachment Using Oracle

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vinesh.

Asked: January 16, 2017 - 11:26 am UTC

Last updated: December 19, 2024 - 6:46 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked 

Team,

Good Monday Morning !!!!!!

we are facing difficulty in sending mail through oracle code, for the data being retrieved from one or more data base tables .(Issue arises when the data is huge)

The records fetched from the query  shall be in the form of HTML Tables. 

we are able to send mail without any issues provided the size of the output data is less.

For our requirement here , lets assume our basic scott user's emp table has 10K records and we are trying to send the contents of the mail through the code. At times we are doing good and in few cases, we are ending up with errors as 

</>
"ORA-06502: PL/SQL: numeric or value error ORA-06512: at "prc_emp_mail_html_list", line 143 ORA-06512: at line 1 "
</>

our requirement is to send the mail successfully without any errors.

our code snippet goes below:

</>

CREATE OR REPLACE PROCEDURE prc_emp_mail_html_list
AS
   p_busdt         DATE                := TRUNC (SYSDATE);
   mailcontent     VARCHAR2 (500);
   sendoraddress   VARCHAR2 (30)       := 'john@xyz.com';
   emailserver     VARCHAR2 (30)       := '192.168.1.5';
   port            NUMBER              := 25;
   conn            UTL_SMTP.connection;
   crlf            VARCHAR2 (2)        := CHR (13) || CHR (10);
   mesg            CLOB;
   mesg_body       CLOB;
   v_cnt           NUMBER;
   v_count         NUMBER              := 0;

   CURSOR c1
   IS
      SELECT *
        FROM emp;
BEGIN
   SELECT COUNT (*)
     INTO v_cnt
     FROM emp;

   conn := UTL_SMTP.open_connection (emailserver);
   UTL_SMTP.helo (conn, emailserver);
   UTL_SMTP.mail (conn, sendoraddress);
   UTL_SMTP.rcpt (conn, 'abc@xyz.com');
   UTL_SMTP.rcpt (conn, 'pqr@xyz.com');

   IF (v_cnt > 0)
   THEN
      mailcontent := 'EMPLOYEES LIST';
      mesg_body :=
            mailcontent
         || mesg_body
         || ' <html>             

<head>            

</head>    


<STYLE TYPE="text/css" MEDIA=screen>       



.columnHeader{ FONT-SIZE: 9pt; COLOR: white; FONT-FAMILY: BookAntiqua;LETTER-SPACING: -0.2pt; FONT-WEIGHT:bolder}     

.rptdata{FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: BookAntiqua;LETTER-SPACING: -0.2pt; }     



</STYLE>              



<BODY bgcolor="#FFFFFF" LINK="#000080">           

<br></br>                 

<TABLE border ="1" cellspacing="1" cellpadding="1" width=400>          

<tr>                

<td         bgcolor="orange" align=center valign=bottom CLASS= columnHeader><b>EMPNO </b></td>                

<td         bgcolor="orange" align=center valign=bottom CLASS= columnHeader><b>ENAME </b></td>                           

<td     bgcolor="orange" align=center valign=bottom CLASS= columnHeader><b>MGR </b></td>             

<td     bgcolor="orange" align=center valign=bottom CLASS= columnHeader><b>HIREDATE</b></td>            
   
<td     bgcolor="orange" align=center valign=bottom CLASS= columnHeader><b>SAL</b></td>            

<td     bgcolor="orange" align=center valign=bottom CLASS= columnHeader><b>DEPTNO</b>                       

                             


</tr>';

      FOR c1rec IN c1
      LOOP
         EXIT WHEN c1%NOTFOUND;
         mesg_body :=
               mesg_body
            || '  <tr bgcolor="#FFF1D9">                                

<td   CLASS = rptdata> <p align =CENTER>'
            || c1rec.empno
            || '</p></td>                                

<td   CLASS = rptdata> <p align =CENTER>'
            || c1rec.ename
            || '</p> </td>                                

<td   CLASS = rptdata> <p align =CENTER>'
            || c1rec.mgr
            || '</p> </td>                                        

<td   CLASS = rptdata> <p align =CENTER>'
            || c1rec.hiredate
            || '</p> </td>  
 
<td   CLASS = rptdata> <p align =CENTER>'
            || c1rec.sal
            || '</p> </td>                    
                
<td   CLASS = rptdata> <p align =CENTER>'
            || c1rec.deptno
            || '</p> </td>                    

</tr>';
         v_count := v_count + 1;
      END LOOP;

      mesg_body :=
            mesg_body
         || crlf
         || crlf
         || crlf
         || '</TABLE> <br><br>'
         || crlf
         || crlf
         || crlf
         || '</TABLE>'
         || '<br><tr >                 

<td  ><b>Regards                 

<BR>John</br></b></td>                 

</tr></br> ';
      mesg :=
            'From:'
         || sendoraddress
         || crlf
         || 'Subject: EMPLOYEES LIST AS ON '
         || TO_CHAR (SYSDATE, 'Dy')
         || ' '
         || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
         || crlf
         || 'To: '
         || 'abc@xyz.com'
         || crlf
         || 'To: '
         || 'prq@xyz.com'
         || crlf
         || 'CC:'
         || 'abcd@xyz.com'
         || crlf
         || crlf
         || crlf
         || mesg_body;
      UTL_SMTP.DATA (conn,
                        'MIME-Version: 1.0'
                     || CHR (13)
                     || CHR (10)
                     || 'Content-type: text/html'
                     || CHR (13)
                     || CHR (10)
                     || mesg
                    );
      UTL_SMTP.quit (conn);
   ELSIF (v_cnt <= 0)
   THEN
      mesg_body :=
            'Hi,'
         || UTL_TCP.crlf
         || '                                 '
         || CHR (13)
         || 'EMPLOYEE DETAILS on '
         || TO_CHAR (p_busdt, 'DD-MON-YYYY')
         || UTL_TCP.crlf
         || '                                 '
         || CHR (13)
         || 'Regards'
         || UTL_TCP.crlf
         || 'John';
      mesg :=
            'From:'
         || sendoraddress
         || crlf
         || 'Subject: EMPLOYEE DETAILS AS ON  '
         || TO_CHAR (SYSDATE, 'Dy')
         || ' '
         || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
         || crlf
         || 'To: '
         || 'abc@xyz.com'
         || crlf
         || 'To: '
         || 'pqr@xyz.com'
         || crlf
         || 'CC:'
         || 'abcD@xyz.com'
         || crlf
         || crlf
         || crlf
         || mesg_body;
      UTL_SMTP.DATA (conn,
                        'MIME-Version: 1.0'
                     || CHR (13)
                     || CHR (10)
                     || 'Content-type: text/html'
                     || CHR (13)
                     || CHR (10)
                     || mesg
                    );
      UTL_SMTP.quit (conn);
   END IF;

   END;

</>


Team, As an alternate effort we have tried to send the contents of the table through attachment,but based on some limitations , if the number of records are huge we are ending with errors.

we have tried with the following code snippet:

</>

CREATE OR REPLACE PROCEDURE emp_mail_attachment
AS
   v_from         VARCHAR2 (80)       := 'abc@xyz.com';
   v_recipient    VARCHAR2 (80)       := 'pqr@xyz.com';
   v_recipient1   VARCHAR2 (80)       := 'abcd@xyz.com';
   v_subject      VARCHAR2 (80)
         := 'List of Employee details On ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy');
   v_mail_host    VARCHAR2 (30)       := '192.168.1.5';
   v_mail_conn    UTL_SMTP.connection;
   crlf           VARCHAR2 (2)        := CHR (13) || CHR (10);
   v_result       LONG;
BEGIN
   v_result := 'EMPNO|ENAME|SAL';

   FOR rec IN (SELECT *
                 FROM emp)
   LOOP
      v_result :=
         v_result || CHR (10) || rec.empno || ',' || rec.ename || ','
         || rec.sal;
   END LOOP;

   v_mail_conn := UTL_SMTP.open_connection (v_mail_host, 25);
   UTL_SMTP.helo (v_mail_conn, v_mail_host);
   UTL_SMTP.mail (v_mail_conn, v_from);
   UTL_SMTP.rcpt (v_mail_conn, v_recipient);
   UTL_SMTP.rcpt (v_mail_conn, v_recipient1);
   UTL_SMTP.DATA (v_mail_conn,
                     'Date: '
                  || TO_CHAR (SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
                  || crlf
                  || 'From: '
                  || v_from
                  || crlf
                  || 'Subject: '
                  || v_subject
                  || crlf
                  || 'To: '
                  || v_recipient
                  || crlf
                  || 'To: '
                  || v_recipient1
                  || crlf
                  || 'Cc:'
                  || v_recipient
                  || crlf
                  || 'MIME-Version: 1.0'
                  || crlf
                  ||                                 -- Use MIME mail standard
                     'Content-Type: multipart/mixed;'
                  || crlf
                  || ' boundary="-----SECBOUND"'
                  || crlf
                  || crlf
                  || '-------SECBOUND'
                  || crlf
                  || 'Content-Type: text/plain;'
                  || crlf
                  || 'Content-Transfer_Encoding: 7bit'
                  || crlf
                  || crlf
                  || ' Hi,'
                  || crlf
                  || '              '
                  || crlf
                  || 'Please find the attached List of employees.'
                  || crlf
                  || '              '
                  || crlf
                  || 'Thanks and Regards,'
                  || crlf
                  || 'DBA_DEVELOPMENT.'
                  || crlf
                  || crlf
                  || '-------SECBOUND'
                  || crlf
                  || 'Content-Type: text/plain;'
                  || crlf
                  || ' name="EMP_SUMMARY.csv"'
                  || crlf
                  || 'Content-Transfer_Encoding: 8bit'
                  || crlf
                  || 'Content-Disposition: attachment;'
                  || crlf
                  || ' filename="EMP_SUMMARY.csv"'
                  || crlf
                  || crlf
                  || v_result
                  || crlf
                  ||                                  -- Content of attachment
                     crlf
                  || '-------SECBOUND--'                      -- End MIME mail
                 );
   UTL_SMTP.quit (v_mail_conn);
END;

</>

Please assist us in sending the mail without any errors either through the text format(records fetched from the query will be in the HTML table format)or as an attachment.

Though we have been using older versions of the package like (utl_smpt), any suggestions with code snippets are most welcome.

Regards,
Vinesh.

and Connor said...

Here's an subset from a routine I used a while back, to email out arbitrary sized html tables. Feel free to customise and use as suits you. 

declare

  c_mail_boundary      constant varchar2(255) default 'a1b2c3d4e3f2g1';
  c_mail_from          constant varchar2(30) := 'noreply@oracle.com';
  c_mail_server        constant varchar2(30) := 'my.mail.server';
  c_mail_footer        constant varchar2(1000) := '</font></body></html>';
  crlf                 constant varchar2(2):= chr(13) || chr(10);


procedure email_init(l_conn in out utl_smtp.connection, p_subject varchar2, p_recip varchar2) is
  l_mesg            varchar2(32767);
  l_db_name         varchar2(10) := substr(sys.database_name,1,4);
begin
  l_conn := utl_smtp.open_connection(c_mail_server, 25);

  utl_smtp.helo(l_conn, c_mail_server);
  utl_smtp.mail(l_conn, c_mail_from );
  utl_smtp.rcpt(l_conn, p_recip);

  l_mesg := l_mesg || 'MIME-Version: 1.0' ||  crlf;
  l_mesg := l_mesg || 'To: ' || p_recip || crlf;
  l_mesg := l_mesg || 'From: ' || c_mail_from||crlf;
  l_mesg := l_mesg || 'Subject: ' || l_db_name || '-' || p_subject|| crlf;
  l_mesg := l_mesg || 'Reply-To: ' || c_mail_from  ||  crlf;
  l_mesg := l_mesg || 'Content-Type: multipart/alternative; boundary=' ||chr(34) || c_mail_boundary ||  chr(34) || crlf;
  l_mesg := l_mesg || '--' || c_mail_boundary || crlf;
  l_mesg := l_mesg || 'content-type: text/html;' || crlf || crlf;
  l_mesg := l_mesg || '<html><head><title>'||p_subject||'</title></head><body>';

  utl_smtp.open_data(l_conn);
  utl_smtp.write_data(l_conn,l_mesg);
end;

procedure email_finalise(l_conn in out utl_smtp.connection) is
  l_mesg            varchar2(32767);
begin
  l_mesg   := crlf || '--' ||  c_mail_boundary || '--' || crlf;
  utl_smtp.write_data(l_conn,l_mesg);
  utl_smtp.write_data(l_conn,c_mail_footer);
  utl_smtp.close_data(l_conn);
  utl_smtp.quit(l_conn);
end;


procedure html_report is
  l_conn utl_smtp.connection;
  l_row_found boolean := false;
begin
  for i in (
    select * from dba_objects where rownum < 20
    )
  loop

    if not l_row_found then
      email_init(l_conn,'My email subject','asktom_us@oracle.com');
      utl_smtp.write_data(l_conn,'<table cellspacing="0" cellpadding="0">');
      l_row_found := true;
    end if;


    utl_smtp.write_data(l_conn,
      '<tr>'||
      '<td width="60"><font face="Calibri">'||i.object_name||
      '<td width="220"><font face="Calibri">'||i.owner||
      '<td width="180"><font face="Calibri">'||i.created||
      '<td width="800"><font face="Calibri">'||i.timestamp||
      '</tr>'||crlf
      );
  end loop;

  if l_row_found then
    utl_smtp.write_data(l_conn,'</table>');
    email_finalise(l_conn);
  end if;
exception
  when others then
    begin utl_smtp.quit(l_conn); exception when others then null; end;
    raise;
end;

begin
  html_report;
end;
/


Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

why not a File link?

Rajeshwaran, Jeyabal, January 18, 2017 - 10:49 am UTC

Any reason to write such a huge contents in email? rather than writing those contents to a file on a directory path and have that file path to be sent in email to the end users? so that the less email size/less bandwidth/less traffic on the network.

Connor McDonald
January 19, 2017 - 1:14 am UTC

Agreed.

Although a lot of email clients now get very suspicious of links / files in content, which can be detrimental to the user experience.

Sending mail through pl/sql code

vinesh, February 06, 2017 - 2:11 pm UTC

Team,

THe sample code snippet has helped us in resolving our issue.

Thanks a ton for your quick response.

Connor McDonald
February 06, 2017 - 2:57 pm UTC

Glad we could help

Sending e-mail to multilple receipients

Gopal, November 21, 2017 - 12:44 pm UTC

Hi, Any idea how to send the e-mail to multiple receipients here.

Thanks.

Gopal
Connor McDonald
November 21, 2017 - 1:33 pm UTC

You call utl_smtp.rcpt multiple times, and adjust the mail header to handle TO and CC, eg a subset here

  utl_smtp.helo(l_conn, c_mail_server);
  utl_smtp.mail(l_conn, c_mail_from );
  utl_smtp.rcpt(l_conn, p_recip1);
  utl_smtp.rcpt(l_conn, p_recip2);
  utl_smtp.rcpt(l_conn, p_recip3);
  utl_smtp.rcpt(l_conn, p_recip4);

  l_mesg := l_mesg || 'To: ' || p_recip1 || crlf;
  l_mesg := l_mesg || 'Cc: ' || p_recip2 || crlf;
  l_mesg := l_mesg || 'Cc: ' || p_recip3 || crlf;
  l_mesg := l_mesg || 'Cc: ' || p_recip4 || crlf;
  l_mesg := l_mesg || 'From: ' || c_mail_from||crlf;
  l_mesg := l_mesg || 'Subject: '|| p_subject|| crlf;

  ...


HTML table not showing

A reader, December 06, 2017 - 7:39 am UTC

Hi Team,

Thanks for this. This is working fine. However the data is not coming in HTML table as expected. Can you please help here.

Thanks;

Regards;
Gopal

Creating dynamic HTML pages

Gopal, February 09, 2018 - 7:36 am UTC

Hi Team,
We are creating an alert framework in database so that
Any sql(SELECT) would run and the output would be e-mailed to specific users. We are using the utl_smtp for that purpose. We are storing html format in a database table so that we create html pages in runtime basing upon this format.The challenge is if we need to change the output basing upon some values like below-
if Col_A value is more than 95% then change the back ground colour of the cell to Red
if Col_A value is between 90 and 95% then change the back ground colour of the cell to Yellow
if Col_A value is Less than 90% change the back ground colour of the cell to Green

We understand this can be achieved in PL/SQ but how to read this logic in run time to create the HTML pages to be e-mailed to the users. Any idea on this please.

Thanks.

Gopal
Connor McDonald
February 09, 2018 - 1:50 pm UTC 

declare

  l_recipient   varchar2(100);  -- fill in whatever you want here
  l_sender      varchar2(100);  -- fill in whatever you want here
  l_subject     varchar2(100);  -- fill in whatever you want here
  l_plaintext   varchar2(100);  -- fill in whatever you want here
  l_html        varchar2(100);
  l_host        varchar2(100) := 'mymailhost.com';
as
  l_conn   utl_smtp.connection;
  l_boundary    varchar2(50) := '----=*#pqowieuryt019283#*=';
begin
  l_html := [your plsql routine to build all the html]

  l_conn := utl_smtp.open_connection(l_host, 25);  -- typical smtp port
  utl_smtp.helo(l_conn, l_host);
  utl_smtp.mail(l_conn, l_sender);
  utl_smtp.rcpt(l_conn, l_recipient);

  utl_smtp.open_data(l_conn);
  
  utl_smtp.write_data(l_conn, 'To: ' || l_recipient || utl_tcp.crlf);
  utl_smtp.write_data(l_conn, 'From: ' || l_sender || utl_tcp.crlf);
  utl_smtp.write_data(l_conn, 'Subject: ' || l_subject || utl_tcp.crlf);
  utl_smtp.write_data(l_conn, 'Reply-To: ' || l_sender || utl_tcp.crlf);
  utl_smtp.write_data(l_conn, 'MIME-Version: 1.0' || utl_tcp.crlf);
  utl_smtp.write_data(l_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || utl_tcp.crlf || utl_tcp.crlf);
  
  if l_plaintext is not null then
    utl_smtp.write_data(l_conn, '--' || l_boundary || utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'content-type: text/plain; charset="iso-8859-1"' || utl_tcp.crlf || utl_tcp.crlf);
    utl_smtp.write_data(l_conn, l_plaintext);
    utl_smtp.write_data(l_conn, utl_tcp.crlf || utl_tcp.crlf);
  end if;

  if l_html is not null then
    utl_smtp.write_data(l_conn, '--' || l_boundary || utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'content-type: text/html; charset="iso-8859-1"' || utl_tcp.crlf || utl_tcp.crlf);
    utl_smtp.write_data(l_conn, l_html);
    utl_smtp.write_data(l_conn, utl_tcp.crlf || utl_tcp.crlf);
  end if;

  utl_smtp.write_data(l_conn, '--' || l_boundary || '--' || utl_tcp.crlf);
  utl_smtp.close_data(l_conn);

  utl_smtp.quit(l_conn);
end;
/


HTML tag rule manager

A reader, February 12, 2018 - 1:10 pm UTC

Thanks for this.

However the html tag like font, width are derived from rule manager as we cannot hard code those in code. Any idea how to achieve this.

Thanks.

Regards;
Gopal
Connor McDonald
February 13, 2018 - 1:31 am UTC

Not sure what you mean. The examples here all (in the end) just send a big of html through email.

The creation/construction of that html is up to you.

Perfect!

Beibit Otegenova, April 03, 2020 - 7:17 am UTC

Thank you so much, Connor McDonald, works perfectly! Exactly what i was looking for.
Chris Saxon
April 03, 2020 - 10:10 am UTC

Glad we could help.

Some question about

Oscar Ivan, June 26, 2020 - 10:57 pm UTC

Hi friends. I hope you can help me.
1. What I have to input into the boundary bin variable, the pass of my mail?
2. If it was my pass, when I run all the script, it returns the error
ERROR en lÝnea 8:
ORA-06550: line 8, column 1:
PLS-00103: Encountered the symbol "AS" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
I'm running this in Oracle 10G.
Best regards
Connor McDonald
June 29, 2020 - 5:55 am UTC

We need to see the entire code

Extremely helpful answer!

Sharvil Desai, December 18, 2024 - 12:45 pm UTC

Thank you so much, Connor!!
Your code to send email in HTML format helped me a lot!
Connor McDonald
December 19, 2024 - 6:46 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here