Package里面发送邮件简单介绍

kuang0405

一定頂了.

Iloveerp

顶起来，等楼主的后续消息了

zhanqiangw

请教楼主两个问题.
1.你的第二个包,能否发送外部邮件,即本域名之外的邮件.
2.我能否在trigger中调用它. 如查能,我应该怎么做.
3.楼主有没有用过,alert发外部邮件,我们的系统设置的是ALERT用UNIX SENDMAIL发送邮件,可是发送不了外部邮件.
请指点,谢谢!

我当前的TRIGGER发邮件是这样写的.

CREATE OR REPLACE TRIGGER OPLK_RCV_TRX20070711 AFTER INSERT OR UPDATE ON RCV_TRANSACTIONS FOR EACH ROW
DECLARE
INVENTORY_ITEM_ID NUMBER;
ONHAND_QTY        NUMBER;
SUBINVENTORY      VARCHAR2(10);
ITEM_ID           NUMBER;
po_number         VARCHAR2(20);
line_num          NUMBER;
item_description  VARCHAR2(240);
item              VARCHAR2(40);
c utl_smtp.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
END;
BEGIN


IF  
:new.transaction_type = 'RETURN TO VENDOR'

then

begin
       c := utl_smtp.open_connection('172.16.128.59');
       utl_smtp.helo(c, 'oplink.com.cn');
       utl_smtp.mail(c, 'zhanqiangw@oplink.com.cn');
       utl_smtp.rcpt(c, 'zhanqiangw@oplink.com.cn');
       utl_smtp.open_data(c);
       send_header('From',    '"Application Administrator" <applprd@oplink.com.cn>');
       send_header('To',      '"Recipient" <zhanqiangw@msn.com>');
       send_header('Subject', 'Returning Material Notification');
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Dear All:');
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF || 'The subinventory have been drived negative, Please have a close check, thanks!');
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);


       utl_smtp.write_data(c, utl_tcp.CRLF || 'Regards/Application Administrator');
       utl_smtp.write_data(c, utl_tcp.CRLF || to_char(sysdate,'dd-Mon-yyyy'));


       utl_smtp.close_data(c);
       utl_smtp.quit(c);
/*  EXCEPTION
       WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
         utl_smtp.quit(c);
         raise_application_error(-20000,
           'Failed to send mail due to the following error11: ' || sqlerrm );
           when others then
         raise_application_error(-20000,
           'Failed to send mail due to the following error31: ' || sqlerrm);*/

    END;

--Dropped Luo Hui on 26-May-2005
/*begin
       c := utl_smtp.open_connection('172.16.128.65');
       utl_smtp.helo(c, 'oplink.com.cn');
       utl_smtp.mail(c, 'applprd@oplink.com.cn');
       utl_smtp.rcpt(c, 'huil@oplink.com.cn');
       utl_smtp.open_data(c);
       send_header('From',    '"Application Administrator" <applprd@oplink.com.cn>');
       send_header('To',      '"Recipient" <huil@oplink.com.cn>');
       send_header('Subject', 'Returning Material Notification');
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Dear All:');
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF || 'The following Billed Po has already been returned, Please have a close check, thanks!');
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Invoice Number From: ' || invoice_number_low || ' To: ' ||invoice_number_high );
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Amount Paid:' || to_char(amount));
       utl_smtp.write_data(c, utl_tcp.CRLF || 'PO Number:' || po_number);
       utl_smtp.write_data(c, utl_tcp.CRLF || 'PO Line Num:' || to_char(line_num));
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Item Number:' || item);
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Item Desc:' || item_description);
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Transaction Type:' || :New.transaction_type);
       utl_smtp.write_data(c, utl_tcp.CRLF || 'Quantity Returned:' || to_char(:new.primary_quantity));
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);
       utl_smtp.write_data(c, utl_tcp.CRLF);


       utl_smtp.write_data(c, utl_tcp.CRLF || 'Regards/Application Administrator');
       utl_smtp.write_data(c, utl_tcp.CRLF || to_char(sysdate,'dd-Mon-yyyy'));


       utl_smtp.close_data(c);
       utl_smtp.quit(c); */
/*  EXCEPTION
       WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
         utl_smtp.quit(c);
         raise_application_error(-20000,
           'Failed to send mail due to the following error11: ' || sqlerrm );
           when others then
         raise_application_error(-20000,
           'Failed to send mail due to the following error31: ' || sqlerrm);*/

--    END;


else
        null;
end if;
exception
          when  No_Data_Found then
                                  null;

end;
/

imzh

最初由 hphubei 发布
[B]


好像是用Alert也可以代替，没有试过， [/B]

确定可以实现。这个我们之前就一直用，比较方便。

terencelin1226

這個我研究好久，
始終悟不出道理，
感謝lz三分享，
我來研究一下，
謝謝。

ys198519

hphuhei大哥太强了，以后小弟有困难就靠你了。

acting

看不明白~~~~~~~

yujialee

非常感谢楼主。 ，您的帖子给了我很大的启发。