There are many tools which can be used to send mail using smtp, but
Stunnel is
quite easy to install and configure.
- Go to stunnel.org and
download the latest Windows binaries
- Install Stunnel ;Remember
path. In my example it is c:\Programe
Files\stunnel
- Edit the file stunnel.conf
located in installation folder to (just backup the original, and replace
all the original text with the text below):
; Use it for client mode
client = yes
[ssmtp]
accept = 1925
connect = smtp.gmail.com:465
Here I use port 1925 on my localhost (unused as far as I
know) to connect to smtp.gmail.com.
Start Stunnel.exe, and test the configuration:
- Start cmd
- Write: telnet localhost 1925
- You should then see something
like "220 mx.google.com ESMTP 5sm18031572eyh.34"
- Write: quit
Troubleshooting: If you cannot reach smtp.gmail.com, there can be any number of
things gone wrong.
- Try a normal ping to
smtp.gmail.com
- Check to see if stunnel.exe
is excepted properly in all firewalls (Windows native and other software
firewalls)
Once stunnel is working, and if you are familiar with UTL_SMTP, don't bother
reading on. This is the same as UTL_SMTP with any other smtp-host requiring
authentication
To create an access control list for your application user,
and enabling it to connect to localhost on port 1925, do the following :
-- create acl
begin
dbms_network_acl_admin.create_acl (
acl => 'gmail.xml',
description => 'Normal
Access',
principal => 'CONNECT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
-- add priviliege
to acl
begin
dbms_network_acl_admin.add_privilege (
acl
=> 'gmail.xml',
principal
=> 'ORBABG',
is_grant
=> TRUE,
privilege
=> 'connect',
start_date
=> null,
end_date
=> null);
end;
-- assign host,
port to acl
begin
dbms_network_acl_admin.assign_acl (
acl => 'gmail.xml',
host => '192.168.240.12',
lower_port => 1925,
upper_port => 1925);
end;
After That create the table from the following
script or from following script :
**********************From Here ***************************
CREATE TABLE SMTPTST
(
CAGRPCDE VARCHAR2(3 BYTE),
CACMPCDE VARCHAR2(3 BYTE),
SENDRADD VARCHAR2(500 BYTE) ,
SENDRPAS VARCHAR2(500 BYTE),
LCLSMTPH VARCHAR2(500 BYTE),
LCLSMTPP INTEGER,
SMTPDOMN VARCHAR2(200 BYTE)
)
TABLESPACE NANO
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
COMMENT ON COLUMN SMTPTST.SENDRPAS IS 'e-mail sender e-mails password for authentication in server ( Its mostly needed in gmail server)';
COMMENT ON COLUMN SMTPTST.LCLSMTPH IS 'Local smtp IP address, where stunnel is used. Generally for sending e-mail we install stunnel on database';
COMMENT ON COLUMN SMTPTST.LCLSMTPP IS 'Local smtp port, where stunnel is used. Generally for sending e-mail we install stunnel on database';
COMMENT ON COLUMN SMTPTST.SMTPDOMN IS 'server domain like gmail.com; if smtp.gmail.com, then gmail.com will be the value of this field';
COMMENT ON COLUMN SMTPTST.SENDRADD IS 'E-mail Sender Address';
**********************Upto Here ***************************
Insert Example Data by using Example Data by following script
**************************Start here***************
SET DEFINE OFF;
Insert into SMTPTST
(CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
Values
('001', '154', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
(CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
Values
('001', '184', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
(CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
Values
('001', '343', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
(CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
Values
('001', '344', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
(CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
Values
('001', '342', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
(CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
Values
('001', '044', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
COMMIT;
****************Up to here*****************************************
Create Package using package script named DATABASE_MAIL.pks
for package header and DATABASE_MAIL.pkb for Body.
****************Start DATABASE_MAIL.pks**********************
CREATE OR REPLACE PACKAGE ORBABG.Database_mail IS
----------------------- Customizable Section -----------------------
-- Customize the SMTP host, port and your domain name below.
sender_mail VARCHAR2(256);
sender_pass VARCHAR2(256);
smtp_host VARCHAR2(256);-- := '192.168.75.232';
smtp_port PLS_INTEGER ;-- := 1925;
smtp_domain VARCHAR2(256);-- := 'gmail.com';
smtp_recepient VARCHAR2(4000);
smtp_cc VARCHAR2(4000);
smtp_bcc VARCHAR2(4000);
smtp_bcc_otner VARCHAR2(1);--O for To and CC and B for Bcc only
-- Customize the signature that will appear in the email's MIME header.
-- Useful for versioning.
MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Orbits UTL_SMTP';
--------------------- End Customizable Section ---------------------
-- A unique string that demarcates boundaries of parts in a multi-part email
-- The string should not appear inside the body of any part of the email.
-- Customize this if needed or generate this randomly dynamically.
BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';
FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
utl_tcp.CRLF;
-- A MIME type that denotes multi-part email (MIME) messages.
MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
BOUNDARY || '"';
MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3;
-- A simple email API for sending email in plain text in a single call.
-- The format of an email address is one of these:
-- someone@some-domain
-- "Someone at some domain" <someone@some-domain>
-- Someone at some domain <someone@some-domain>
-- The recipients is a list of email addresses separated by
-- either a "," or a ";"
--For Getting SMTP Host which is database
PROCEDURE mail(
recipients IN VARCHAR2,
cc in Varchar2 DEFAULT NULL,
bcc In Varchar2 DEFAULT NULL,
subject IN VARCHAR2,
iSender In VARCHAR2,
iSenderPass In VARCHAR2,
ismtp_host In VARCHAR2,
ismtp_port In pls_integer,
ismtp_domain In VARCHAR2,
message IN VARCHAR2,
i_bcc_other In varchar2);
-- Extended email API to send email in HTML or plain text with no size limit.
-- First, begin the email by begin_mail(). Then, call write_text() repeatedly
-- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
-- email in non-ASCII or multi-byte character set. End the email with
-- end_mail().
FUNCTION begin_mail(
recipients IN VARCHAR2,
cc IN VARCHAR2 DEFAULT NULL,
bcc IN VARCHAR2 DEFAULT NULL,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL,
iSender IN VARCHAR2,
iSenderPass IN VARCHAR2,
ismtp_host IN VARCHAR2,
ismtp_port IN PLS_INTEGER ,
ismtp_domain IN VARCHAR2 ,
i_bcc_other In varchar2
)
RETURN utl_smtp.connection;
-- Write email body in ASCII
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);
-- Write email body in non-ASCII (including multi-byte). The email body
-- will be sent in the database character set.
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);
-- Write email body in binary
PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
message IN RAW);
-- APIs to send email with attachments. Attachments are sent by sending
-- emails in "multipart/mixed" MIME format. Specify that MIME format when
-- beginning an email with begin_mail().
-- Send a single text attachment.
PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
data IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE);
-- Send a binary attachment. The attachment will be encoded in Base-64
-- encoding format.
PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
data IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE);
-- Send an attachment with no size limit. First, begin the attachment
-- with begin_attachment(). Then, call write_text repeatedly to send
-- the attachment piece-by-piece. If the attachment is text-based but
-- in non-ASCII or multi-byte character set, use write_mb_text() instead.
-- To send binary attachment, the binary content should first be
-- encoded in Base-64 encoding format using the this package for 8i,
-- or the native one in 9i. End the attachment with end_attachment.
PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL);
-- End the attachment.
PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE);
-- End the email.
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);
-- Extended email API to send multiple emails in a session for better
-- performance. First, begin an email session with begin_session.
-- Then, begin each email with a session by calling begin_mail_in_session
-- instead of begin_mail. End the email with end_mail_in_session instead
-- of end_mail. End the email session by end_session.
FUNCTION begin_session RETURN utl_smtp.connection;
-- Begin an email in a session.
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL);
-- End an email in a session.
PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);
-- End an email session.
PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);
END;
/
**************End Of DATABASE_MAIL.pks****************
**************DataBase_MAil.pkb ***********
CREATE OR REPLACE PACKAGE BODY ORBABG.Database_mail IS
-- Return the next email address in the list of email addresses, separated
-- by either a "," or a ";". The format of mailbox may be in one of these:
-- someone@some-domain
-- "Someone at some domain" <someone@some-domain>
-- Someone at some domain <someone@some-domain>
FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
addr VARCHAR2(256);
i pls_integer;
FUNCTION lookup_unquoted_char(str IN VARCHAR2,
chrs IN VARCHAR2) RETURN pls_integer AS
c VARCHAR2(5);
i pls_integer;
len pls_integer;
inside_quote BOOLEAN;
BEGIN
inside_quote := false;
i := 1;
len := length(str);
WHILE (i <= len) LOOP
c := substr(str, i, 1);
IF (inside_quote) THEN
IF (c = '"') THEN
inside_quote := false;
ELSIF (c = '\') THEN
i := i + 1; -- Skip the quote character
END IF;
GOTO next_char;
END IF;
IF (c = '"') THEN
inside_quote := true;
GOTO next_char;
END IF;
IF (instr(chrs, c) >= 1) THEN
RETURN i;
END IF;
<<next_char>>
i := i + 1;
END LOOP;
RETURN 0;
END;
BEGIN
addr_list := ltrim(addr_list);
i := lookup_unquoted_char(addr_list, ',;');
IF (i >= 1) THEN
addr := substr(addr_list, 1, i - 1);
addr_list := substr(addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;
i := lookup_unquoted_char(addr, '<');
IF (i >= 1) THEN
addr := substr(addr, i + 1);
i := instr(addr, '>');
IF (i >= 1) THEN
addr := substr(addr, 1, i - 1);
END IF;
END IF;
RETURN addr;
END;
-- Write a MIME header
PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
END;
-- Mark a message-part boundary. Set <last> to TRUE for the last boundary.
PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE) AS
BEGIN
IF (last) THEN
utl_smtp.write_data(conn, LAST_BOUNDARY);
ELSE
utl_smtp.write_data(conn, FIRST_BOUNDARY);
END IF;
END;
------------------------------------------------------------------------
PROCEDURE mail(
recipients IN VARCHAR2,
cc IN VARCHAR2 DEFAULT NULL,
bcc IN VARCHAR2 DEFAULT NULL,
subject IN VARCHAR2,
iSender In VARCHAR2,
iSenderPass In VARCHAR2,
ismtp_host In VARCHAR2,
ismtp_port In pls_integer,
ismtp_domain In VARCHAR2,
message IN VARCHAR2,
i_bcc_other In varchar2) IS
conn utl_smtp.connection;
BEGIN
conn := begin_mail( recipients,cc,bcc, subject,
'text/plain',Null,iSender,
iSenderPass,ismtp_host,ismtp_port,
ismtp_domain,i_bcc_other);
write_text(conn, message);
end_mail(conn);
END;
------------------------------------------------------------------------
FUNCTION begin_mail(
recipients IN VARCHAR2,
cc IN VARCHAR2 DEFAULT NULL,
bcc IN VARCHAR2 DEFAULT NULL,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL,
iSender IN VARCHAR2,
iSenderPass IN VARCHAR2,
ismtp_host IN VARCHAR2,
ismtp_port IN pls_integer ,
ismtp_domain IN VARCHAR2 ,
i_bcc_other In varchar2
)
RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
sender_mail :=iSender;
sender_pass :=iSenderPass;
smtp_host :=ismtp_host;
smtp_port :=ismtp_port;
smtp_domain :=ismtp_domain;
smtp_recepient:=recipients;
smtp_cc :=cc;
smtp_bcc :=bcc;
smtp_bcc_otner :=i_bcc_other;
conn := begin_session;
begin_mail_in_session(conn, subject, mime_type,
priority);
RETURN conn;
END;
------------------------------------------------------------------------
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(conn, message);
END;
------------------------------------------------------------------------
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2) IS
BEGIN
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
END;
------------------------------------------------------------------------
PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
message IN RAW) IS
BEGIN
utl_smtp.write_raw_data(conn, message);
END;
------------------------------------------------------------------------
PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
data IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE) IS
BEGIN
begin_attachment(conn, mime_type, inline, filename);
write_text(conn, data);
end_attachment(conn, last);
END;
------------------------------------------------------------------------
PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
data IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE) IS
i PLS_INTEGER;
len PLS_INTEGER;
BEGIN
begin_attachment(conn, mime_type, inline, filename, 'base64');
-- Split the Base64-encoded attachment into multiple lines
i := 1;
len := utl_raw.length(data);
WHILE (i < len) LOOP
IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
utl_smtp.write_raw_data(conn,
utl_encode.base64_encode(utl_raw.substr(data, i,
MAX_BASE64_LINE_WIDTH)));
ELSE
utl_smtp.write_raw_data(conn,
utl_encode.base64_encode(utl_raw.substr(data, i)));
END IF;
utl_smtp.write_data(conn, utl_tcp.CRLF);
i := i + MAX_BASE64_LINE_WIDTH;
END LOOP;
end_attachment(conn, last);
END;
------------------------------------------------------------------------
PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL) IS
BEGIN
write_boundary(conn);
write_mime_header(conn, 'Content-Type', mime_type);
IF (filename IS NOT NULL) THEN
IF (inline) THEN
write_mime_header(conn, 'Content-Disposition',
'inline; filename="'||filename||'"');
ELSE
write_mime_header(conn, 'Content-Disposition',
'attachment; filename="'||filename||'"');
END IF;
END IF;
IF (transfer_enc IS NOT NULL) THEN
write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
END IF;
utl_smtp.write_data(conn, utl_tcp.CRLF);
END;
------------------------------------------------------------------------
PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE) IS
BEGIN
utl_smtp.write_data(conn, utl_tcp.CRLF);
IF (last) THEN
write_boundary(conn, last);
END IF;
END;
------------------------------------------------------------------------
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
end_mail_in_session(conn);
end_session(conn);
END;
------------------------------------------------------------------------
FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
nls_charset Varchar2(255);
receipient Varchar2(4000);
p_smtp_recepient Varchar2(4000):=smtp_recepient;
p_smtp_cc Varchar2(4000):=smtp_cc;
p_smtp_bcc Varchar2(4000):=smtp_bcc;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
select value
into nls_charset
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
-- utl_smtp.command(conn, 'STARTTLS');
utl_smtp.command(conn, 'auth login');
utl_smtp.command(conn,utl_encode.text_encode(sender_mail, nls_charset, 1));
utl_smtp.command(conn, utl_encode.text_encode(sender_pass, nls_charset, 1));
utl_smtp.command(conn, 'MAIL FROM: <'||sender_mail||'>');
If smtp_bcc_otner='O' then
WHILE (p_smtp_recepient IS NOT NULL) LOOP
utl_smtp.command(conn, 'RCPT TO: <'||get_address(p_smtp_recepient)||'>'||chr(13));
END LOOP;
WHILE (p_smtp_cc IS NOT NULL) LOOP
utl_smtp.command(conn, 'RCPT TO: <'||get_address(p_smtp_cc)||'>'||chr(13));
END LOOP;
Else
WHILE (p_smtp_bcc IS NOT NULL) LOOP
utl_smtp.command(conn, 'RCPT TO: <'||get_address(p_smtp_bcc)||'>');
END LOOP;
End If;
RETURN conn;
END;
------------------------------------------------------------------------
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL) IS
nls_charset varchar2(255);
BEGIN
-- Specify sender's address (our server allows bogus address
-- as long as it is a full email address (xxx@yyy.com).
/*utl_smtp.mail(conn, get_address(my_sender));*/
-- Specify recipient(s) of the email.
/* WHILE (my_recipients IS NOT NULL) LOOP
utl_smtp.rcpt(conn, get_address(my_recipients));
END LOOP;*/
-- Start body of email
utl_smtp.open_data(conn);
-- Set "From" MIME header
write_mime_header(conn, 'From', sender_mail);--barack@obama.com
-- Set "To" MIME header
If smtp_bcc_otner='O' then
write_mime_header(conn, 'To', smtp_recepient);
write_mime_header(conn, 'cc', smtp_cc);
Else
write_mime_header(conn, 'To', smtp_recepient);
write_mime_header(conn, 'cc', smtp_cc);
write_mime_header(conn, 'Bcc', smtp_bcc);
End If;
-- Set "Subject" MIME header
write_mime_header(conn, 'Subject', subject);
-- Set "Content-Type" MIME header
write_mime_header(conn, 'Content-Type', mime_type);
-- Set "X-Mailer" MIME header
write_mime_header(conn, 'X-Mailer', MAILER_ID);
-- Set priority:
-- High Normal Low
-- 1 2 3 4 5
IF (priority IS NOT NULL) THEN
write_mime_header(conn, 'X-Priority', priority);
END IF;
-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
utl_smtp.write_data(conn, utl_tcp.CRLF);
IF (mime_type LIKE 'multipart/mixed%') THEN
write_text(conn, 'This is a multi-part message in MIME format.' ||
utl_tcp.crlf);
END IF;
END;
------------------------------------------------------------------------
PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
utl_smtp.close_data(conn);
END;
------------------------------------------------------------------------
PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
utl_smtp.quit(conn);
END;
END;
/
***********DATABSE_MAIL.pkb**********
Create TEST_PACKAGE_MAIL.prc procedure
to test your e-mail from database.
CREATE OR REPLACE procedure ORBABG.Test_Package_mail is
conn utl_smtp.connection;
req utl_http.req;
resp utl_http.resp;
data RAW(200);
oSenderMail Varchar2(600);
oSenderMlPas Varchar2(600);
oLclSmtpHost Varchar2(600);
oLclSmtpPort pls_integer;
oLclSmtpDom Varchar2(600);
f_message varchar2(32767);
f_message1 varchar2(32767);
f_message2 varchar2(32767);
BEGIN
smtp_port_domain('344',oSenderMail,oSenderMlPas,oLclSmtpHost,oLclSmtpPort,oLclSmtpDom);
conn := Database_mail.begin_mail(
recipients => 'mrifai@etainfotech.com',
cc => 'akter@etainfotech.com',
bcc => 'rashel@etainfotech.com',
subject => 'Attachment Test BCC',
mime_type => Database_mail.MULTIPART_MIME_TYPE,
iSender => oSenderMail,
iSenderPass => oSenderMlPas,
ismtp_host => oLclSmtpHost,
ismtp_port => oLclSmtpPort,
ismtp_domain => oLclSmtpDom,
i_bcc_other =>'O');
/* conn := Database_mail.begin_mail(
recipients => 'akter@etainfotech.com',
cc => 'akter_cs@yahoo.com',
bcc => 'rashel@etainfotech.com',
subject => 'Attachment Test BCC',
mime_type => Database_mail.MULTIPART_MIME_TYPE,
iSender => oSenderMail,
iSenderPass => oSenderMlPas,
ismtp_host => oLclSmtpHost,
ismtp_port => oLclSmtpPort,
ismtp_domain => oLclSmtpDom,
i_bcc_other =>'B'); */
f_message:= ' <table border="1" style="width: 100%; font-family: Tahoma; font-size: smaller;
border-right: dimgray 1px solid; border-top: dimgray 1px solid; border-left: dimgray 1px
solid; border-bottom: dimgray 1px solid;" cellpadding="5" cellspacing="0">
<tr>
<td colspan="3" style="text-align: center; border-top-style: none;
border-right-style: none; border-left-style: none; border-bottom-style: none; font-weight:
bold; font-size: large; text-decoration: underline;">
DHAKA BANGLADESH</td>
</tr>
</table>';
f_message1:= '<html>
<head>
<script type="text/javascript">
function printpage()
{
window.print()
}
</script>
</head>
<body>'||f_message||'
<input type="button" value="Print" onclick="printpage()" />
</body>
</html>';
f_message2:= '<html>
<head>
</head>
<body>'||f_message||'
</body>
</html>';
Database_mail.attach_text(
conn => conn,
data => f_message2,
mime_type => 'text/html');
Database_mail.attach_text(
conn => conn,
data => f_message1,
mime_type => 'text/html',
inline => FALSE,
filename => 'report.htm',
last => TRUE);
Database_mail.end_mail( conn => conn );
END;
/
**************************************************************************
Now smtp_port_domain procedure details below
CREATE OR REPLACE PROCEDURE smtp_port_domain(iCampcde VARCHAR2,
oSenderMail Out Varchar2,
oSenderMlPas Out Varchar2,
oLclSmtpHost Out Varchar2,
oLclSmtpPort Out pls_integer,
oLclSmtpDom Out Varchar2)
IS
BEGIN
Begin
SELECT SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN
INTO oSenderMail,oSenderMlPas,oLclSmtpHost,oLclSmtpPort,oLclSmtpDom
FROM SMTPTST
WHERE CAGRPCDE='001' And CACMPCDE=iCampcde and rownum=1;
Exception
When Others Then
Raise_Application_Error (-20001,sqlerrm);
End;
END ;
/
Now using TEST_PACKAGE_MAIL procedure, you can send e-mail to any e-mail. Its fully tested and ready to use.
