CREATE OR REPLACE PROCEDURE "SYS"."SEND_MAIL"
(
pSender VARCHAR2,
pRecipient VARCHAR2,
pSubject VARCHAR2,
pMessage VARCHAR2)
IS
mailhost CONSTANT VARCHAR2(30) := 'YOUR MAIL SYSTEM IP OR FQDN';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg VARCHAR2(1000);
mail_conn UTL_SMTP.CONNECTION;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf || 'From: <'|| pSender ||'' || crlf || 'Subject: '|| pSubject || crlf || 'To: '||pRecipient || crlf || '' || crlf || pMessage;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, pSender);
utl_smtp.rcpt(mail_conn, pRecipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
END send_mail;
使用范例
SQL desc send_mail
PROCEDURE send_mail
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PSENDER VARCHAR2 IN
PRECIPIENT VARCHAR2 IN
PSUBJECT VARCHAR2 IN
PMESSAGE VARCHAR2 IN
SQL exec send_mail('apple','chu@odba.idv.tw','Test','hello')
PL/SQL procedure successfully completed.
上面的范例是利用远端的mail service来寄信，简单又容易实做!!
