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 create procedure, Drop any procedure to RoleName; --RoleName角色名稱

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

create or replace procedure mailsender(mail_to varchar2, mail_cc varchar2,mail_subject varchar2, mail_content varchar2)

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;

arrow
arrow
    全站熱搜

    Big Bear 發表在 痞客邦 留言(0) 人氣()