使用 Oracle UTL_MAIL

2012-10-22

28159
0
DB(Oracle)

使用 Oracle UTL_MAIL

1. 用PL/SQL(以SYS角色進入,username:SYS,Password:...,Connect as:SYSDBA)

2.安裝UTL_MAIL

2.1 開啟UTLMAIL.SQL, 剪下 CREATE OR REPLACE PACKAGE utl_mail AS ... end;, 貼到PL/SQL中執行

2.2 開啟UTLMAIL.SQL, 剪下 CREATE OR REPLACE PUBLIC SYNONYM utl_mail FOR sys.utl_mail;, 貼到PL/SQL中執行

2.3 開啟prvtmail.plb, 剪下CREATE OR REPLACE PACKAGE BODY utl_mail wrapped ... /, 用"END;"取代"/", 貼到PL/SQL中執行

3.設定SMTP Server

貼到PL/SQL中執行: alter system set smtp_out_server = 'Domain name(or IP):PORT' scope = Both;

4.設定其他role使用utl_mail權限


grant execute on sys.utl_tcp to RoleName;
grant execute on sys.utl_smtp to RoleName;
grant execute on sys.utl_mail to RoleName;

-----------------------------------------------------------------------------------------------------------------------------------

使用procedure來寄送mail


as

mail_header varchar2(120) := '<HTML><HEAD><META http-equiv=Content-type content="text/html;charset=UTF-8"></HEAD><BODY Style="font-size:10pt;">;
mail_footer varchar2(100) := '<br/><br/><br/><p>Best Regards,</p><p>big bear</p></BODY></HTML>';

begin
    sys.utl_mail.send(sender=>'abc@abc.com', --mail server
    recipients=>mail_to,
    recipients=>mail_cc,
    subject=>mail_subject,
    message=>mail_header||mail_content||mail_footer,
    mime_type=>'text/html; charset=UTF-8');
end mailsender;

回首頁