How to send Email in Oracle using Gmail.

Email sending by using Oracle through Gmail:

In oracle we can send email by using two ways.

1- Send Email through Gmail

2- Send Email through Email Exchange server.

In this post we will know how to send email by using oracle through gmail.

Small compnies can not purchase and manage exchange serves so they can use this method by using gmail.

1- We have to install this small software that wil use for as a small exchnage server for gmail, it is third party tool but very usefull tool for gmail.

stunnel-4.56-installer

2- After installing this software got to the installed location like C:\Program Files\stunnel and open this file in notepad “stunnel.conf” and set this configuration.

[SSMTP]
CLIENT = YES
ACCEPT  = 1925
CONNECT = SMTP.GMAIL.COM:465

3- Check that Gmail is Pinging with this command in MS Dos

TELNET LOCALHOST 1925
PING SMTP.GMAIL.COM

4- Create Package in Oracle Data Base

— Package Specs

CREATE OR REPLACE PACKAGE EMAIL_GMAIL
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 SEND (
P_SENDER IN VARCHAR2
, P_RECIPIENT IN VARCHAR2
, P_SUBJECT IN VARCHAR2
, P_MESSAGE IN VARCHAR2
);
END;
/

— Package Body

CREATE OR REPLACE PACKAGE BODY HR.EMAIL_GMAIL 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 SEND (
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);
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_ENCODE.TEXT_ENCODE(‘yourgmailaddress@gmail.com’, NLS_CHARSET, 1));
UTL_SMTP.COMMAND(L_CONN, UTL_ENCODE.TEXT_ENCODE(‘your_gmail_pswd, NLS_CHARSET, 1));
— SET FROM/RECIPIENT
UTL_SMTP.COMMAND(L_CONN, ‘MAIL FROM: <‘||P_SENDER||’>’);
UTL_SMTP.COMMAND(L_CONN, ‘RCPT TO: <‘||P_RECIPIENT||’>’);
— 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;
/

In this package put your Gmail Address and Password in it in these two lines.

UTL_SMTP.COMMAND(L_CONN,UTL_ENCODE.TEXT_ENCODE(‘YourGmailAddress@gmail.com’, NLS_CHARSET, 1));
UTL_SMTP.COMMAND(L_CONN, UTL_ENCODE.TEXT_ENCODE(‘YourGmailPassword’, NLS_CHARSET, 1));

5- Run this statement in oracle database.

EXEC EMAIL_GMAIL.SEND (‘YourGmailAddress’,’AnyEmailAddress’,’Your email subject’,’Email Message body’);

When you run this then an email will send to that address and you can also check that sending email in your gmail in send items.
Also you can check what is the status of sending email in “stunnel.exe” software.

Congratulation you send email by using gmail and Oracle.

You can call this funtion in any procedure and Oracle Form just pass the parameter and use it.
ORACLE_GMAIL
If you want feel any problem just comment me.
Thanks