Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

E-Mail using PL/SQL and Gmail

This is something I tried out for myself by following the steps on Havard Kristiansen’s blog. If you are thinking of doing something similar, I would suggest reading his post. The major difference in my case was that I did this on a Oracle 9.2.0.6 database running on RHEL4. The changes that this entailed are:

  1. Since RHEL4 comes with stunnel installed, so all I had to do was to create the stunnel.conf file in /etc/stunnel. The contents of the file is the same as what Havard mentioned. Once that was done, I had to start the stunnel service by using the command ‘stunnel &’.
  2. utl_encode.text_encode is not available in 9.2.0.6 hence I used UTL_ENCODE.BASE64_ENCODE instead

Apart from that I tweaked to code to support multiple recipients. Below is the code that I used:

create or replace package apex_mail_p
is
 g_smtp_host varchar2 (256) := 'localhost';
 g_smtp_port pls_integer := 1925;
 g_smtp_domain varchar2 (256) := 'gmail.com';
 g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
 -- send mail using UTL_SMTP
 procedure mail (
 p_sender in varchar2
 , p_recipient in varchar2
 , p_subject in varchar2
 , p_message in varchar2
 );
end;
/

create or replace package body apex_mail_p
is
 -- Write a MIME header
 procedure write_mime_header (
 p_conn in out nocopy utl_smtp.connection
 , p_name in varchar2
 , p_value in varchar2
 )
 is
 begin
 utl_smtp.write_data ( p_conn
 , p_name || ': ' || p_value || utl_tcp.crlf
 );
 end;
 procedure mail (
 p_sender in varchar2
 , p_recipient in varchar2
 , p_subject in varchar2
 , p_message in varchar2
 )
 is
 l_conn utl_smtp.connection;
 nls_charset varchar2(255);
 p_to varchar2(250);
 j number:=null;
 p_recipient_store varchar2(4000);
 begin
 -- get characterset
 select value
 into nls_charset
 from nls_database_parameters
 where parameter = 'NLS_CHARACTERSET';
 -- establish connection and autheticate
 l_conn := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
 utl_smtp.ehlo(l_conn, g_smtp_domain);
 utl_smtp.command(l_conn, 'auth login');
 utl_smtp.command(l_conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('youraccount@gmail.com'))));
 utl_smtp.command(l_conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('yourpassword'))));
 -- set from/recipient
 utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
 --loop through all reciepients and issue the RCPT TO command for each one
 p_recipient_store:=p_recipient;
 while nvl(length(p_recipient_store),0)>0
 loop
 select decode(instr(p_recipient_store, ','),
 0,
 length(p_recipient_store) + 1,
 instr(p_recipient_store, ','))
 into j
 from dual;
 p_to:=substr(p_recipient_store,1,j-1);
 utl_smtp.command(l_conn, 'RCPT TO: <'||p_to||'>');
 p_recipient_store:=substr(p_recipient_store,j+1);
 end loop;
 -- write mime headers
 utl_smtp.open_data (l_conn);
 write_mime_header (l_conn, 'From', p_sender);
 write_mime_header (l_conn, 'To', p_recipient);
 write_mime_header (l_conn, 'Subject', p_subject);
 write_mime_header (l_conn, 'Content-Type', 'text/plain');
 write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
 utl_smtp.write_data (l_conn, utl_tcp.crlf);
 -- write message body
 utl_smtp.write_data (l_conn, p_message);
 utl_smtp.close_data (l_conn);
 -- end connection
 utl_smtp.quit (l_conn);
 exception
 when others
 then
 begin
 utl_smtp.quit(l_conn);
 exception
 when others then
 null;
 end;
 raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);
 end;
end;
/

To send mail to multiple recipients, call the procedure with the proper parameters:

begin
 apex_mail_p.mail('Oracleappsnotes', 'firstrecipient@example.com,secondrecipient@example.com,thirdrecipient@example.com,', 'Something Interesting', 'Sending mail through Gmail');
end;
Advertisements

3 responses to “E-Mail using PL/SQL and Gmail

  1. iman October 3, 2013 at 2:48 pm

    Error when running script:
    ORA-20000: Failed to send mail due to the following error: ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. vz4sm9200661pab.11 – gsmtp
    ORA-06512: at “APEX_MAIL_P”, line 77
    ORA-06512: at line 2

  2. Nestor January 22, 2015 at 10:42 pm

    hi I am facing exactly the same error

    do you have a solution for this issue?

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: