The UTL_SMTP package is extremely useful in that it will allow email messages to
be sent from the database.
However, it is still a bit cumbersome in that it will require users to
know the SMTP server connection sequence
in order to send out messages.
The following procedure simplifies things in that it will allow the user to send
email supplying only
the following:
FROM:,TO:,Mail Serve Host,Mail Port,SUBJECT,and BODY
Keep in mind, however, that you will NEED an SMTP server that will relay mail from
the Database in order for this procedure to work.
REM
REM Begin PLSQL
REM
CREATE OR REPLACE PROCEDURE SEND_EMAIL(sendoraddress IN varchar2,
receiveraddress IN varchar2,
emailserver IN varchar2,
port in number,
subject in varchar2,
message_body in varchar2)
IS
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
message_body_out varchar2(4000);
BEGIN
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, ReceiverAddress);
message_body_out:= 'Date: '||TO_CHAR(SYSDATE, 'MM DD YYYY HH:MI:SS' )|| crlf ||
'From:'||SendorAddress|| crlf ||
'Subject: '||subject || crlf ||
'To: '||ReceiverAddress || crlf ||
''||message_body;
utl_smtp.data( conn, message_body_out );
utl_smtp.quit( conn );
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
