一个可以在Oracle发信的Procedure范例

        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来寄信，简单又容易实做!!

查看本文来源