[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!