Forum Stats

  • 3,855,602 Users
  • 2,264,531 Discussions
  • 7,906,104 Comments

Discussions

Output of Query to be sent in Email Body

Bhavin Mamtora
Bhavin Mamtora Member Posts: 90
edited Mar 15, 2016 2:26AM in SQL & PL/SQL

Hello Team,

I have created a below mentioned procedure to send the output of the query in an email attachment periodically and it is working fine.

However, I would like to know how to send the output of the query in an email body not an attachment in the form of table as below:


Sample output of query:


NAV Date NAV_VALUEGOLD PRICECASH COMPONENT
01-Feb-162612.08592726.5835-114.4976
02-Feb-162620.98732735.9827-114.9954
03-Feb-162646.57852762.8658-116.2873
04-Feb-162667.54322784.903-117.3598

CREATE OR REPLACE

PROCEDURE PR_NAV_MAILING

AS

  V_FROM        VARCHAR2(80) := '[email protected]';

  v_Recipient_5 VARCHAR2(80) := '[email protected]';

  V_SUBJECT     VARCHAR2(80) := 'Units creation data ';

  v_Mail_Host   VARCHAR2(30) := 'SMTP IP ADDRESS';

  v_Mail_Conn utl_smtp.Connection;

  crlf VARCHAR2(2) := chr(13)||chr(10);

  CURSOR INS_UPD

  IS

    --QUERY to capture UNITS creation data

    SELECT NAV_DATE,

      NAV_VALUE,

      subscription ,

      redemption

    FROM data_nav

    WHERE scheme_code = '974'

    AND NAV_DATE      >ADD_MONTHS(SYSDATE,-1)

    ORDER BY nav_date DESC;

BEGIN

  v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host);

  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_5);

  --OPEN DATA CONNNECTION

  UTL_SMTP.OPEN_DATA(v_mail_conn);

  --MAIL HEADER

  utl_smtp.write_DATA(v_Mail_Conn,'Date: ' || TO_CHAR(sysdate, 'DD-MON-YYYY hh:mi:ss AM') || crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'From: ' || v_From || crlf );

  Utl_Smtp.Write_Data(V_Mail_Conn,'Subject: '|| V_Subject ||' Auto Generated Mail '||Crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'Bcc: ' || v_Recipient_5 ||','|| crlf);

  --MAIL BODY

  utl_smtp.write_DATA(v_Mail_Conn,'MIME-Version: 1.0'|| crlf );

  utl_smtp.write_DATA(v_Mail_Conn,'Content-Type: multipart/mixed;'|| crlf );

  utl_smtp.write_DATA(v_Mail_Conn,' boundary="-----SECBOUND"'|| crlf ||crlf );

  utl_smtp.write_DATA(v_Mail_Conn,'-------SECBOUND'|| crlf );

  utl_smtp.write_DATA(v_Mail_Conn,'Content-Type: text/plain;'|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'Content-Transfer_Encoding: 7bit'|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,NULL|| crlf);

  UTL_SMTP.WRITE_DATA(V_MAIL_CONN,NULL|| CRLF);

  utl_smtp.write_DATA(v_Mail_Conn,NULL|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'Dear All, '|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'Please find the attached data for units creation. '||crlf);

  utl_smtp.write_DATA(v_Mail_Conn,NULL|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,NULL|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'Regards, '|| crlf);

  UTL_SMTP.WRITE_DATA(V_MAIL_CONN,NULL|| CRLF);

  utl_smtp.write_DATA(v_Mail_Conn,'Operations Team. '|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,NULL|| crlf);

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'This mail is Auto generated.');

  --MAIL ATACHMENT

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF || '-------SECBOUND');

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Type: text/csv; ');

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Disposition: attachment; filename="Gold_ETF NAV'||'.csv"');

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Transfer-Encoding: 8bit');

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF);

  utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'NAV Date, NAV_VALUE,GOLD PRICE,CASH COMPONENT');

  FOR cur_rec IN INS_UPD

  LOOP

    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF || cur_rec.NAV_Date || ',' || cur_rec.NAV_VALUE || ',' || cur_rec.SUBSCRIPTION || ',' || cur_rec.REDEMPTION );

  END LOOP;

  utl_smtp.write_DATA(v_Mail_Conn,NULL|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,' '|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,' '|| crlf);

  --CLOSE CONNECTION

  UTL_SMTP.CLOSE_DATA(v_mail_conn);

  utl_smtp.Quit(v_mail_conn);

EXCEPTION

WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error THEN

  RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: '||SQLERRM);

END;

Tagged:
Infantraj

Best Answer

  • Infantraj
    Infantraj Member Posts: 116
    edited Mar 14, 2016 8:10AM Answer ✓

    Hi,

    Instead of using   utl_smtp.write_DATA(v_Mail_Conn,'Content-Type: text/plain;'|| crlf); use  'text/html' for the content type and write the data in the HTML Table...


    For example:

    utl_smtp.write_data(v_Mail_Conn,'<html>'

                   || 'Hello,'

                   || '<br>'

                   || '<br>'

                   || p_first_name

                   || ' '

                   || p_last_name

                   || ' has been hired'

                   || p_start_date

                   || '. This employees data has been successfully loaded to Oracle.'

                   || '<br>'

                   || '<br>'

                   || 'If you have any questions or concerns please email.'

                   || '<br>'

                   || '<br>'

                   || '</html>');

    You can use your cursor column name instead of the p_first_name and etc.

    You can write this in your program to create HTML table

    utl_smtp.write_data(v_Mail_Conn,'<html>           

    <body>           

    <table border="1" style="background-color: lavender; width: 1000px;" ><tr><th><font size="3">First Name</font></th><th><font size="3">Number</font></th><th><font size="3">Error Message</font></th></tr>');

    Thanks

    Infant

    Bhavin Mamtora

Answers

  • Paulzip
    Paulzip Member Posts: 8,759 Blue Diamond
    edited Mar 14, 2016 7:55AM

    It seems you've already attempted it in your proc.  What isn't working?

  • BluShadow
    BluShadow Member, Moderator Posts: 42,372 Red Diamond
    edited Mar 14, 2016 8:05AM

    You probably want to do something like setting the mime type of your email as text/html and then constructing html table elements to populate with your data etc.

    Bhavin Mamtora
  • Unknown
    edited Mar 14, 2016 8:06AM

    UTL_SMTP will do whatever you program it to do.

  • Bhavin Mamtora
    Bhavin Mamtora Member Posts: 90
    edited Mar 14, 2016 8:10AM

    Hello Paulzip,

    Procedure is working fine for attachment, I want to send the output as part of the email message body like below:

    Dear All,
    Please find below the data for Units creation.


    NAV Date NAV_VALUEGOLD PRICECASH COMPONENT
    01-Feb-162612.08592726.5835-114.4976
    02-Feb-162620.98732735.9827-114.9954
    03-Feb-162646.57852762.8658-116.2873
    04-Feb-162667.54322784.903-117.3598

    Regards,

    Operations Team.

    This mail is auto generated.

  • Infantraj
    Infantraj Member Posts: 116
    edited Mar 14, 2016 8:10AM Answer ✓

    Hi,

    Instead of using   utl_smtp.write_DATA(v_Mail_Conn,'Content-Type: text/plain;'|| crlf); use  'text/html' for the content type and write the data in the HTML Table...


    For example:

    utl_smtp.write_data(v_Mail_Conn,'<html>'

                   || 'Hello,'

                   || '<br>'

                   || '<br>'

                   || p_first_name

                   || ' '

                   || p_last_name

                   || ' has been hired'

                   || p_start_date

                   || '. This employees data has been successfully loaded to Oracle.'

                   || '<br>'

                   || '<br>'

                   || 'If you have any questions or concerns please email.'

                   || '<br>'

                   || '<br>'

                   || '</html>');

    You can use your cursor column name instead of the p_first_name and etc.

    You can write this in your program to create HTML table

    utl_smtp.write_data(v_Mail_Conn,'<html>           

    <body>           

    <table border="1" style="background-color: lavender; width: 1000px;" ><tr><th><font size="3">First Name</font></th><th><font size="3">Number</font></th><th><font size="3">Error Message</font></th></tr>');

    Thanks

    Infant

    Bhavin Mamtora
  • Bhavin Mamtora
    Bhavin Mamtora Member Posts: 90
    edited Mar 14, 2016 9:46AM

    Hello BluShadow,

    Please guide me on how to construct html table elements.

    I tried searching online , but could not get any reference to constructing table in email message body.

    Thanks & Regards,

    Bhavin Mamtora

  • Bhavin Mamtora
    Bhavin Mamtora Member Posts: 90
    edited Mar 14, 2016 10:00AM

    hello InfantRaj,

    I got a jist of how to enter the table in email, could you guide me to the links which can help me understand the meaning of below code like how to add more columns, text color, populate data etc.

    <table border="1" style="background-color: lavender; width: 1000px;" ><tr><th><font size="3">First Name</font></th><th><font size="3">Number</font></th><th><font size="3">Error Message</font></th></tr>');

    Thanks & Regards,

    Bhavin Mamtora. 

  • Bhavin Mamtora
    Bhavin Mamtora Member Posts: 90
    edited Mar 14, 2016 10:35AM

    I was able to achieve the required results with some research on html and help of code provided by Infantraj.

    Thanks a lot , BluShadow and InfantRaj for showing me the right direction.

    Thanks & Regards,

    Bhavin Mamtora.

    Infantraj
  • Infantraj
    Infantraj Member Posts: 116
    edited Mar 15, 2016 2:26AM

    Thanks Bhavin....

    Infant Raj

This discussion has been closed.