一個可以在Oracle發信的Procedure范例
2024-08-29 13:42:32
供稿：網友
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來寄信，簡單又輕易實做!!
