15/06/2013

[PL/SQL] Oracle send mail through SMTP server with authentication

Sending mail through a PL/SQL script in Oracle is pretty easy thanks to the UTL_SMTP package.

Much like TELNET, sending a mail via our script will require us to use the HELO, MAIL FROM, RCPT TO and DATA commands. Thus, when sending a mail to multiple recipients - directly or in CC - we must issue the RCPT TO command once for each one and add the CC mail header to our message in order for them to appear as such.

A simple script would be:

CREATE OR REPLACE TYPE t_ccs AS TABLE OF VARCHAR2;--create an object "list of CCs"

Then define our procedure:

 CREATE OR REPLACE PROCEDURE sendMail (  
      smtpHost     IN     VARCHAR2,  
      smtpPort     IN PLS_INTEGER     DEFAULT 25,  
      mailFrom     IN     VARCHAR2,  
      rcptTo     IN     VARCHAR2,  
      ccs     IN     t_ccs,  
      messageSubject     IN     VARCHAR2,  
      messageBody     IN     VARCHAR2)  
 IS  
  l_conn     UTL_SMTP.connection;  
  l_ccs     VARCHAR2(2000);  
 BEGIN  
      --open connection  
      l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);  
      UTL_SMTP.helo(l_conn, smtpHost);  
        
      --prepare headers  
      UTL_SMTP.mail(l_conn, mailFrom);  
      UTL_SMTP.rcpt(l_conn, rcptTo);  
   
      /*if we have multiple recipients or CCs, we must call UTL_SMTP.rcpt once for each one  
      however, we shall specify that there are CCs in the mail header in order for them to appear as such*/  
      IF ccs IS NOT NULL THEN  
           FOR i IN ccs.FIRST..ccs.LAST LOOP  
                UTL_SMTP.rcpt(l_conn, ccs(i));--add recipient  
                l_ccs:=l_ccs||ccs(i)||',';--mark as CC  
           END LOOP;  
           --now remove the trailing comma at the end of l_ccs  
           l_ccs:=substr(l_ccs,0,length(l_ccs)-1 );  
      END IF;  
   
      --start multi line message  
      UTL_SMTP.open_data(l_conn);  
   
      --prepare mail header  
      /*DO NOT USE MON instead of MM in the date pattern if you run the script on machines with different locales as it will be misunderstood  
      and the mail date will appear as 01/01/1970*/  
      UTL_SMTP.write_data(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || UTL_TCP.crlf);  
      UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || UTL_TCP.crlf);  
      UTL_SMTP.write_data(l_conn, 'Cc: ' || l_ccs || UTL_TCP.crlf);  
      UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || UTL_TCP.crlf);  
      UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || UTL_TCP.crlf || UTL_TCP.crlf);  
        
      --include the message body  
      UTL_SMTP.write_data(l_conn, messageBody || UTL_TCP.crlf || UTL_TCP.crlf);  
        
      --send the email  
      UTL_SMTP.close_data(l_conn);  
      UTL_SMTP.quit(l_conn);  
 END;  


And call it:

 DECLARE  
      l_ccs t_ccs;  
 BEGIN  
      ccs:=t_ccs('cc1@mail.com', 'ccN@mail.com');--if necessary add CCs  
        
      sendMail (  
           smtpHost     => 'mail.example.com',  
           smtpPort     => 25,  
           mailFrom     => 'sender@example.com',  
           rcptTo     => 'recipient@example.com',  
           ccs     => l_ccs,  
           messageSubject     => 'Test subject',  
           messageBody     => 'Test body');  
             
 EXCEPTION WHEN OTHERS THEN  
                DBMS_OUTPUT.PUT_LINE('Sending failed: '||SQLERRM);  
 END;  


Note that the mail may not be immediately sent so wait some 5-15 min before declaring failure.

If the SMTP server you're using requires authentication, you need to modify the procedure; supposing you need to use the AUTH LOGIN method, you must pass the base64 encoded username and password to the server after the EHLO (NOT HELO) command.

Add 2 parameters to our procedure:

username IN VARCHAR2,
password IN VARCHAR2

Now replace:

      l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);  
      UTL_SMTP.helo(l_conn, smtpHost);  


with:

 l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));  
 l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));  
 l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);  
 UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO  
 UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');  
 UTL_SMTP.command(l_conn, l_encoded_username);  
 UTL_SMTP.command(l_conn, l_encoded_password);  


And you're done. Don't forget to pass username and password as parameters when calling the procedure now!

7 comments:

  1. Very good indeed, thanks

    ReplyDelete
  2. Hello,
    I've used your procedure but the results aren't great. The message has no subject, and the "To:" and the "Subject" are within the body of the message. The message header is populated with the "From", "To", and "Date", there no "Subject".
    I commented out the CC's, which I won't use anyway, and the Date to simplify, but the results are still wrong.

    Many Thanks for looking at this.


    Here's the procedure I adopted.
    CREATE OR REPLACE PROCEDURE sendMail (
    smtpHost in varchar2,
    smtpPort in pls_integer default 587,
    mailFrom in varchar2,
    rcptTo in varchar2,
    -- ccs in varchar2,
    messageSubject in varchar2,
    messageBody in varchar2,
    username in varchar2,
    password in varchar2
    )
    IS
    l_conn UTL_SMTP.connection;
    -- l_ccs varchar2(2048);
    L_ENCODED_USERNAME varchar2(2048);
    L_ENCODED_PASSWORD varchar2(2048);
    crlf varchar2(2) := chr(10) || chr(13);

    BEGIN
    --open connection
    -- l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
    -- UTL_SMTP.helo(l_conn, smtpHost);
    l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
    l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));
    l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
    UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO
    UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');
    UTL_SMTP.command(l_conn, l_encoded_username);
    UTL_SMTP.command(l_conn, l_encoded_password);

    --prepare headers
    UTL_SMTP.mail(l_conn, mailFrom);
    UTL_SMTP.rcpt(l_conn, rcptTo);

    /*if we have multiple recipients or CCs, we must call UTL_SMTP.rcpt once for each one
    however, we shall specify that there are CCs in the mail header in order for them to appear as such*/
    -- IF ccs IS NOT NULL THEN
    -- FOR i IN ccs.FIRST .. ccs.LAST LOOP
    -- UTL_SMTP.rcpt(l_conn, ccs(i));--add recipient
    -- l_ccs:=l_ccs||ccs(i)||',';--mark as CC
    -- END LOOP;
    --now remove the trailing comma at the end of l_ccs
    -- l_ccs:=substr(l_ccs,0,length(l_ccs)-1 );
    -- END IF;

    --start multi line message
    UTL_SMTP.open_data(l_conn);

    --prepare mail header
    /*DO NOT USE MON instead of MM in the date pattern if you run the script on machines with different locales as it will be misunderstood
    and the mail date will appear as 01/01/1970*/
    -- UTL_SMTP.write_data(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || crlf);
    UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || crlf);
    -- UTL_SMTP.write_data(l_conn, 'Cc: ' || ccs || crlf);
    UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || crlf);
    UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || crlf);

    --include the message body
    UTL_SMTP.write_data(l_conn, messageBody || crlf || crlf);

    --send the email
    UTL_SMTP.close_data(l_conn);
    UTL_SMTP.quit(l_conn);
    END;

    ReplyDelete
    Replies
    1. Hello,

      I think there may be something wrong with your setup as this code is being successfully used in a production environment. We're using Oracle 11g, a custom SMTP server, and the recipients are both GMail and custom domains.

      Note that the code is equivalent to this example: https://en.wikipedia.org/wiki/Simple_Mail_Transfer_Protocol#SMTP_transport_example where you can see that the fields such as subject or cc, which should be shown separately when opening an email, are actually passed within the message body; it is the client's burden to understand and correctly display them. There's no other way to send that "metadata" information.

      Please have a look at the UTL_SMTP package documentation too: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_smtp.htm

      You may try to:
      - send the email to another recipient domain
      - use another SMTP server for testing purposes, eg SMTPDummy: http://www.cylog.org/tools/smtpdummy.jsp which allows you to see the actual message being sent, including the headers
      - print out the data passed to the write_data calls before invoking them, to make sure you're not somehow passing empty strings
      - open the message via web interface or another desktop client. Sometimes they may display the message differently

      Best regards

      Delete
  3. --Here's the call to that procedure
    DECLARE
    l_ccs varchar2(2048);
    t_ccs varchar2(2048);
    ccs varchar2(2048);
    BEGIN
    ccs:='';--if necessary add CCs

    sendMail (
    smtpHost => 'mail.mydomain.org',
    smtpPort => 587,
    mailFrom => 'mailbox@mydomainorg',
    rcptTo => 'mypersonalmail@hotmail.com',
    messageSubject => 'Test subject at 7 57',
    messageBody => 'Test body',
    username => 'username@mydomain.org',
    password => 'mypassword');

    EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Sending failed: '||SQLERRM);
    END;

    ReplyDelete
  4. nice, this worked for me. before it Ive got issue with RDNS no entry. Thanks bro you save my day.

    ReplyDelete

With great power comes great responsibility