?
Solved

Problem sending Email from Oracle(UTL_SMTP)

Posted on 2008-10-20
4
Medium Priority
?
1,356 Views
Last Modified: 2013-12-07
hi,
I am trying to send a mail from Oracle (UTL_SMTP).
Mail is getting sent, but Body of the email is ABSENT (Subject is present).
Code is as follows
BEGIN
      --Creating a connection with SMTP Server
      g_mail_conn := UTL_SMTP.open_connection (g_s_mailhost, 25);
      --Handshaking
      UTL_SMTP.helo (g_mail_conn, g_s_mailhost);
      --Setting the FROM ID
      UTL_SMTP.mail (g_mail_conn, p_i_msg_from);
      --Setting the TO ID
      UTL_SMTP.rcpt (g_mail_conn, p_i_msg_to);
      --Preparing the mail
      l_mesg :=
            'Date: '
         || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
         || 'From: '
         || p_i_msg_from
         || UTL_TCP.crlf
         || 'To: '
         || p_i_msg_to
         || UTL_TCP.crlf
         || 'Subject: '
         || p_i_msg_subject
         || UTL_TCP.crlf
         || p_i_msg_text;
      --Setting the format of the mail as HTML
      UTL_SMTP.DATA (g_mail_conn,
                       'MIME-Version: 1.0'
                     || CHR (13)
                     || CHR (10)
                     || 'Content-type: text/html'
                     || CHR (13)
                     || CHR (10)
                     || l_mesg || CHR (13)
                    );
      --Closing the connection
      UTL_SMTP.quit (g_mail_conn);


There are some other procs also for sending mail.
They used to work fine earlier.
But now SMTP server is reconfigured for the machine.

Please help.
Thanks
0
Comment
Question by:bhavanisharansingh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1500 total points
ID: 22757547
rather than trying to put all of the header and body together using the DATA routine,  try splitting it into pieces.   not only will this reduce the likelihood of varchar2 overflow from concatenation, but it will also allow you to pinpoint where in the procedure the errors are occuring.


try this...
BEGIN
    --Creating a connection with SMTP Server
    g_mail_conn   := UTL_SMTP.open_connection(g_s_mailhost, 25);
    --Handshaking
    UTL_SMTP.helo(g_mail_conn, g_s_mailhost);
    --Setting the FROM ID
    UTL_SMTP.mail(g_mail_conn, p_i_msg_from);
    --Setting the TO ID
    UTL_SMTP.rcpt(g_mail_conn, p_i_msg_to);
    --Preparing the mail
 
    UTL_SMTP.open_data(g_mail_conn);
    UTL_SMTP.write_data(g_mail_conn,
                        'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || UTL_TCP.crlf);
    UTL_SMTP.write_data(g_mail_conn, 'From: ' || p_i_msg_from || UTL_TCP.crlf);
    UTL_SMTP.write_data(g_mail_conn, 'To: ' || p_i_msg_to || UTL_TCP.crlf);
    UTL_SMTP.write_data(g_mail_conn, 'Subject: ' || p_i_msg_subject || UTL_TCP.crlf);
    UTL_SMTP.write_data(g_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
 
    UTL_SMTP.write_data(g_mail_conn, 'Content-type: text/html' || UTL_TCP.crlf);
 
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
 
    UTL_SMTP.write_data(g_mail_conn, p_i_msg_text);
 
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
 
    --Closing the connection
    UTL_SMTP.close_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.quit(g_mail_conn);

Open in new window

0
 

Author Comment

by:bhavanisharansingh
ID: 22764162
Thats great. It worked buddy. Thanks a lot :).
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22764301
glad I could help, just accept the above answer and assign a grade to close the question
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22764467
why only a B?  if it worked and was complete, it should have been an A.  

If you needed more, you only needed to ask.  Please don't assign penalties without giving the volunteers (not just me) a chance to complete your answer.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question