Snippet Name: Send email from Oracle
Description: With Oracle8i, you can send e-mail messages directly from PL/SQL using the UTL_TCP or UTL_SMTP packages. Note that JServer needs to be installed and configured for this to work.
Usage examples:
set serveroutput on
exec send_mail(msg_to =>'[email protected]');
exec send_mail(msg_to =>'[email protected]', -
msg_text=>'Sample email text from the Oracle database' -
);
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 27th, 2009
|
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from VARCHAR2 := 'oracle',
msg_to VARCHAR2,
msg_subject VARCHAR2 := 'Email alert from the Oracle database',
msg_text VARCHAR2 := '' )
IS
c UTL_TCP.connection;
rc INTEGER;
BEGIN
c := UTL_TCP.open_connection('127.0.0.1', 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;
/
show errors
|
