Sending Email from PLSQL from Gmail

articles: 

How to send Email in Oracle using Gmail.
Email sending by using Oracle through Gmail:

BEGIN EMAIL_GMAIL.SEND ('shabbier.sa@gmail.com','shabbier.sa@gmail.com','Your email subject','Emaisaaasdasal Message body'); END;

*
ERROR at line 1:
ORA-20000: Failed to send mail due to the following error: ORA-29278: SMTP
transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.EMAIL_GMAIL", line 52
ORA-06512: at line 1

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));

But At the End I am Getting This Error

BEGIN EMAIL_GMAIL.SEND ('shabbier.sa@gmail.com','shabbier.sa@gmail.com','Your email subject','Emaisaaasdasal Message body'); END;

*
ERROR at line 1:
ORA-20000: Failed to send mail due to the following error: ORA-29278: SMTP
transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.EMAIL_GMAIL", line 52
ORA-06512: at line 1