Salim berbagi…. tempat belajar dan berinteraksi….


Send Email Stored Procedure
March 16, 2011, 4:02 AM
Filed under: ORACLE

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from    varchar2,
msg_to      varchar2,
msg_subject varchar2,
msg_text    varchar2)
IS
c  utl_tcp.connection;
rc integer;
BEGIN
/*
msg_from –> Sender, ex: ‘abc_sender@domain.com’
msg_to –> Receiver, ex: ‘xyz@yahoo.com’ or ‘xyz@yahoo.com; abc@gmail.com’
msg_subject –> Subject of Email, ex: ‘Training Notification’
msg_text –> Body of Email, ex: ‘Dear, Kindly please be informed that…..’
*/

c := utl_tcp.open_connection(‘mailservername.domain’, 25);       — open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘HELO localhost’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘MAIL FROM: ‘||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘RCPT TO: ‘||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘DATA’);                 — Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
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
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, ‘QUIT’);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
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 because of: ‘||
sqlerrm);
END;
/


Leave a Comment so far
Leave a comment



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: