oracle send mail,数据库包send_mail发邮件的配置含ACL和privilege

短命女 2022-10-21 12:59 30阅读 0赞

1.    先创建一个SEND\_EMAIL存储过程

create or replace PROCEDURE SEND\_EMAIL (p\_From IN VARCHAR2 :='mdssysmail@mic.com.tw'

,p\_To IN VARCHAR2

,p\_Subject IN VARCHAR2

,p\_Body IN VARCHAR2

,p\_Cc IN VARCHAR2 := NULL

,p\_Bcc IN VARCHAR2 := NULL

,p\_ContentType IN VARCHAR2 := 'text/plain;charset=gb2312'

,p\_MailIp IN VARCHAR2 := '118.244.235.30'

,p\_Port IN NUMBER := 25) IS

v\_Connection utl\_smtp.connection;

v\_Data RAW(32767);

v\_email varchar2(100);

v\_count number;

v\_start number;

v\_end   number;

v\_date  varchar2(50);

v\_user\_name varchar2(50) := 'mdssysmail@mic.com.tw';

v\_password varchar2(50) := 'Password\_1';

BEGIN

v\_date:=to\_char(sysdate-1/3,'YYYY-mm-DD HH24:MI:SS','NLS\_DATE\_LANGUAGE=AMERICAN');

v\_Connection := UTL\_smtp.open\_connection(p\_MailIp,p\_Port);

UTL\_smtp.ehlo(v\_Connection, p\_MailIp);

UTL\_smtp.command(v\_Connection, 'AUTH LOGIN');

UTL\_smtp.command(v\_Connection,

demo\_base64.encode(utl\_raw.cast\_to\_raw(v\_user\_name)));

UTL\_smtp.command(v\_Connection,

demo\_base64.encode(utl\_raw.cast\_to\_raw(v\_password)));

UTL\_smtp.mail(v\_Connection, p\_From);

IF (p\_to IS NOT NULL) THEN

v\_count := 1;

v\_start := 0;

v\_end :=1;

for c in 1..length(p\_to) loop

if c>v\_start then

v\_end := instr(p\_to,';',1,v\_count);

if v\_end>0 then

UTL\_smtp.rcpt(v\_Connection,substr(p\_to,v\_start+1,v\_end-v\_start-1));

else

UTL\_smtp.rcpt(v\_Connection,substr(p\_to,v\_start+1,length(p\_to)-v\_start));

exit;

end if;

v\_start := v\_end;

v\_count := v\_count +1;

end if;

end loop;

END IF;

IF (p\_Cc IS NOT NULL) THEN

v\_count := 1;

v\_start := 0;

v\_end :=1;

for c in 1..length(p\_Cc) loop

if c>v\_start then

v\_end := instr(p\_Cc,';',1,v\_count);

if v\_end>0 then

UTL\_smtp.rcpt(v\_Connection,substr(p\_Cc,v\_start+1,v\_end-v\_start-1));

else

UTL\_smtp.rcpt(v\_Connection,substr(p\_Cc,v\_start+1,length(p\_Cc)-v\_start));

exit;

end if;

v\_start := v\_end;

v\_count := v\_count +1;

end if;

end loop;

END IF;

IF (p\_Bcc IS NOT NULL) THEN

v\_count := 1;

v\_start := 0;

v\_end :=1;

for c in 1..length(p\_Bcc) loop

if c>v\_start then

v\_end := instr(p\_Bcc,';',1,v\_count);

if v\_end>0 then

UTL\_smtp.rcpt(v\_Connection,substr(p\_Bcc,v\_start+1,v\_end-v\_start-1));

else

UTL\_smtp.rcpt(v\_Connection,substr(p\_Bcc,v\_start+1,length(p\_Bcc)-v\_start));

exit;

end if;

v\_start := v\_end;

v\_count := v\_count +1;

end if;

end loop;

END IF;

UTL\_smtp.open\_data(v\_Connection);

\--select to\_char(sysdate,'YYYY-MON-DD','NLS\_DATE\_LANGUAGE=AMERICAN') from dual

/\* \*\* Sending the header information \*/

\--UTL\_smtp.write\_data(v\_Connection,'Date: ' || to\_char(sysdate-1/3,'YYYY-MON-DD hh24:mi:ss','NLS\_DATE\_LANGUAGE=AMERICAN') ||UTL\_tcp.CRLF);

UTL\_smtp.write\_data(v\_Connection,'Date: ' || TO\_CHAR(SYSTIMESTAMP,'DD Mon YYYY HH24:MI:SS TZHTZM','NLS\_DATE\_LANGUAGE=AMERICAN') ||UTL\_tcp.CRLF);

\--UTL\_smtp.write\_data(v\_Connection,'Date: ' || v\_date ||UTL\_tcp.CRLF);

\--UTL\_smtp.write\_data(v\_Connection,'Date: ' || 'NOW' || UTL\_tcp.CRLF);

IF (p\_From IS NOT NULL) THEN

UTL\_smtp.write\_data(v\_Connection, 'From: ' ||p\_From|| UTL\_tcp.CRLF);

END IF;

IF (p\_to IS NOT NULL) THEN

UTL\_smtp.write\_data(v\_Connection, 'To: ' || p\_to|| UTL\_tcp.CRLF);

END IF;

IF (p\_Cc IS NOT NULL) THEN

UTL\_smtp.write\_data(v\_Connection, 'Cc: ' || p\_cc || UTL\_tcp.CRLF);

END IF;

IF (p\_Bcc IS NOT NULL) THEN

UTL\_smtp.write\_data(v\_Connection, 'Bcc: ' || p\_bcc || UTL\_tcp.CRLF);

END IF;

\--UTL\_smtp.write\_data(v\_Connection, 'Subject: ' || p\_Subject || UTL\_tcp.CRLF);  //以下可以顯示中文subject

UTL\_smtp.write\_raw\_data(v\_Connection,utl\_raw.cast\_to\_raw(convert('Subject: ' ||p\_Subject||utl\_tcp.crlf,'ZHS16GBK')));

UTL\_smtp.write\_data(v\_Connection, 'MIME-Version: ' || '1.0' || UTL\_tcp.CRLF);

UTL\_smtp.write\_data(v\_Connection, 'Content-Type: ' ||p\_ContentType|| UTL\_tcp.CRLF);

UTL\_smtp.write\_data(v\_Connection, 'Content-Transfer-Encoding: ' || '8bit' || UTL\_tcp.CRLF);

/\* \*\* End of header information \*/

UTL\_smtp.write\_data(v\_Connection, UTL\_tcp.CRLF);

/\* \*\* Actual body is sent here \*/

v\_Data := utl\_raw.cast\_to\_raw(convert(p\_body,'ZHS16GBK'));

UTL\_smtp.write\_raw\_data(v\_Connection, v\_Data);

/\* \*\* Connection is closed here \*/

UTL\_smtp.close\_data(v\_Connection);

UTL\_smtp.quit(v\_Connection);

EXCEPTION

WHEN UTL\_smtp.transient\_error OR UTL\_smtp.permanent\_error THEN

UTL\_smtp.quit(v\_Connection);

dbms\_output.put\_line(sqlerrm);

WHEN OTHERS THEN

UTL\_smtp.quit(v\_Connection);

dbms\_output.put\_line(sqlerrm);

END SEND\_EMAIL;

2.    配置ACL和privilege

已经创建了ACL和privilege的情况下1.查看邮件配置的ACL和ip

select \* from dba\_network\_acls

2.查看邮件配置的privilege是什么

select acl,principal,privilege,is\_grant,to\_char(start\_date, 'dd-mon-yyyy') as start\_date,to\_char(end\_date, 'dd-mon-yyyy') as end\_date

fromdba\_network\_acl\_privileges;

3.在邮件配置中添加该用户

begin

DBMS\_NETWORK\_ACL\_ADMIN.ADD\_PRIVILEGE(

acl=> '第一步查到的ACL',

principal => '用户名大写',

is\_grant  => TRUE,

privilege => '第二步查到的privilege');

end;

commit;

4.测试，进入用户下进行测试，如果在sys用户下测试，则send\_email前面要加用户名

如果在putty上发，中文可能乱码，可以在plsql上发就不会乱码了

如果第一步查到的acl对应10.98.0.211则用如下测试

begin

SEND\_EMAIL  ('mdssysmail@mic.com.tw'

,'luxus.liao@mic.com.tw'

,'KANGDA OK'

,'luxus.liao@mic.com.tw'

,  'text/plain;charset=gb2312'

, '10.98.0.211'

, 25);

end;

如果第一步查到的acl对应10.98.10.120，则用如下测试

begin

SEND\_EMAIL  ('mdssysmail@mdsets.cn'

,'luxus.liao@mic.com.tw'

,'KANGDA OK'

,'luxus.liao@mic.com.tw'

,  'text/plain;charset=gb2312'

, '10.98.10.120'

, 25);

end;

没有创建了ACL和privilege的情况下

1.创建 ACL

begin

DBMS\_NETWORK\_ACL\_ADMIN.CREATE\_ACL ( acl=>'mail\_acl.xml', description=>'用户名大写', principal=>'用户名大写大写', is\_grant=>TRUE, privilege=> 'connect');

END;

commit;

2.与邮件服务关联

BEGIN

DBMS\_NETWORK\_ACL\_ADMIN.ASSIGN\_ACL (

acl         => 'mail\_acl.xml',

host        => '客户邮件服务器IP',

lower\_port  => 25);

END;

commit;

3.邮件配置关联用户

begin

DBMS\_NETWORK\_ACL\_ADMIN.ADD\_PRIVILEGE(

acl=> 'mail\_acl.xml',

principal => '用户名大写',

is\_grant  => TRUE,

privilege => 'connect');

end;

commit;

4.邮件测试

begin

SEND\_EMAIL  ('客户那边的一个发件人'

,'luxus.liao@mic.com.tw'

,'KANGDA OK'

,'luxus.liao@mic.com.tw'

,  'text/plain;charset=gb2312'

, '客户邮件服务器IP'

, 25);

end;

DBMS\_NETWORK\_ACL\_ADMIN.CREATE\_ACL中privilege的解释

Network privilege to be granted or denied - 'connect |

resolve' (case sensitive). A database user needs the connect

privilege to an external network host computer if he or she is

connecting using the UTL\_TCP, UTL\_HTTP, UTL\_SMTP, and UTL\_

MAIL utility packages. To resolve a host name that was given a

host IP address, or the IP address that was given a host name,

with the UTL\_INADDR package, grant the database user the

resolve privilege.

oracle send mail,数据库包send_mail发邮件的配置含ACL和privilege

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 linux发邮件失败,Linux SendMail发送邮件失败诊断案例，sendmail发送邮件

相关 oracle send mail,数据库包send_mail发邮件的配置含ACL和privilege

相关 oracle 邮件过程,Oracle 发邮件过程

相关用.NET提供的Mail来发邮件

相关 Django之send_mail发送邮件激活账户

相关 Linux 通过sendmail 发邮件到外部邮箱

相关 mail_send.php

相关 sendmail发邮件报错的解决办法

相关 sendMail 发邮件 tls

相关 shell 发邮件命令之 sendmail