oracle tips & tricks

Oracle Tips & Tricks

 
Monday, 10 March 2008
Sending E-Mail in PL/SQL
Here some example code you can use for sending email from within PL/SQL
CREATE OR REPLACE PROCEDURE SEND_MAIL_TCP (
msg_from VARCHAR2 := 'sender@testing.com'
, msg_to VARCHAR
, msg_subject VARCHAR2 := 'E-Mail message from your database'
, msg_text VARCHAR2 := ''
)
IS
c UTL_TCP.CONNECTION;
rc INTEGER;
BEGIN
c := UTL_TCP.OPEN_CONNECTION('localhost', 25); -- open the SMTP port 25 on local machine
rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
rc := UTL_TCP.WRITE_LINE(c, 'DATA'); -- Start message body
rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
rc := UTL_TCP.WRITE_LINE(c, '');
rc := UTL_TCP.WRITE_LINE(c, msg_text);
rc := UTL_TCP.WRITE_LINE(c, '.'); -- End of message body
rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
UTL_TCP.CLOSE_CONNECTION(c); -- Close the connection
EXCEPTION
WHEN others THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

Labels:

posted by popo @ 23:12  
0 Comments:
Post a Comment
<< Home
 
 
Search

Google
Previous Post
Category
Blogrush
Blogroll
Legal
Archives
Template by
Free Blogger templates