使用PLSQL发送电子邮件 [英] Send Email Using PLSQL

问题描述

我想使用PL_SQL通过gmail或yahoo主机发送电子邮件，我在google中搜索并找到了SMT.Mail软件包，但它对我没有用，请任何人可以指导我如何实现这一目标?

I want to Send Email via gmail or yahoo host using PL_SQL, i searched in google and find SMT.Mail package but it did't work for me, Please any one can guide me how will i achieve this goal ?

CREATE OR REPLACE PROCEDURE 
      send_mail (sender    IN VARCHAR2, 
                 recipient IN VARCHAR2, 
                 message   IN VARCHAR2, 
                 nStatus   OUT NUMBER) 
IS 
    mailhost    VARCHAR2(30) := 'smtp.gmail.com '; -- host mail addr 
    mail_conn  utl_smtp.connection; 
BEGIN 
    nStatus := 0; 
    mail_conn := utl_smtp.open_connection(mailhost, 25); 
    utl_smtp.helo(mail_conn, mailhost); 
    utl_smtp.mail(mail_conn, sender); 
    utl_smtp.rcpt(mail_conn, recipient); 
    utl_smtp.data(mail_conn, message); 
    utl_smtp.quit(mail_conn); 
EXCEPTION 
    WHEN OTHERS THEN 
        nStatus := SQLCODE; 
END send_mail;

当我测试此过程时，我得到:ORA-29278: SMTP transient error: 421 Service not available

when i test this procedure I get: ORA-29278: SMTP transient error: 421 Service not available

推荐答案

我的经验是utl_mail软件包更易于使用.这是一个愚蠢的例子:

My experience has been that the utl_mail package is much easier to use. Here is a silly example:

BEGIN
   UTL_MAIL.send (sender     => '[e-mail]'
                , recipients => '[e-mail]'
                , subject    => 'Goofy Messages'
                , MESSAGE    => 'Please don''t send any more goofy messages'
                , mime_type  => 'text/html; charset=us-ascii');
 END;

必须将系统参数smtp_out_server设置为电子邮件服务器的名称.

You must set the system parameter smtp_out_server to the name of your email server.

对于Oracle 11G R2及更高版本，您必须设置适当的访问控制列表(ACL)才能使其正常工作.这是我用来执行此操作的代码.

With Oracle 11G R2 and beyond, you must set up the proper access control list (ACL) for this to work. This is the code that I use to do so.

DECLARE
   -- ACL name to be used for email access reuse the same value for all 
   -- future calls
   l_acl         VARCHAR2 (30) := 'utl_smtp.xml';
   -- Oracle user to be given permission to send email
   l_principal   VARCHAR2 (30) := 'CEAADMIN';
   -- Name of email server
   g_mailhost    VARCHAR2 (60) := 'mail.yourserver.com';
   l_cnt         INTEGER;

   PROCEDURE validate_smtp_server
   AS
      l_value       v$parameter.VALUE%TYPE;
      l_parameter   v$parameter.name%TYPE := 'smtp_out_server';
   BEGIN

      SELECT VALUE
        INTO l_value
        FROM v$parameter
       WHERE name = l_parameter;

      IF l_value IS NULL
      THEN
         raise_application_error (
            -20001
          ,    'Oracle parameter '
            || l_parameter
            || ' has not been set'
            || UTL_TCP.crlf
            || 'it s/b mail.yourserver.com'
         );
      END IF;

      DBMS_OUTPUT.put_line ('parameter ' || l_parameter || ' value is ' ||     l_value);

   END validate_smtp_server;

   PROCEDURE create_if_needed (p_acl IN VARCHAR2)
   AS
      l_cnt   INTEGER;
   BEGIN

      SELECT COUNT (*) c
        INTO l_cnt
        FROM dba_network_acls a
       WHERE SUBSTR (acl, INSTR (acl, '/', -1) + 1) = p_acl;

      IF l_cnt = 0
      THEN
         DBMS_OUTPUT.put_line ('creating acl ' || p_acl);
         DBMS_NETWORK_ACL_ADMIN.create_acl (
            acl         => p_acl
          , description => 'Allow use of utl_smtp'
          , principal   => l_principal
          , is_grant    => TRUE
          , privilege   => 'connect'
         );

         DBMS_NETWORK_ACL_ADMIN.assign_acl (acl => p_acl, HOST => g_mailhost);
         COMMIT;
      ELSE
         DBMS_OUTPUT.put_line (p_acl || ' acl already exists');
      END IF;

   END create_if_needed;

   PROCEDURE add_if_needed (
      p_principal   IN VARCHAR2
    , p_acl         IN VARCHAR2
   )
   AS
      l_cnt   INTEGER;
   BEGIN

      SELECT COUNT (*) c
        INTO l_cnt
        FROM dba_network_acl_privileges
       WHERE SUBSTR (acl, INSTR (acl, '/', -1) + 1) = p_acl
         AND principal = p_principal;

      IF l_cnt = 0
      THEN
         DBMS_NETWORK_ACL_ADMIN.add_privilege (
            acl       => 'utl_smtp.xml'
          , principal => p_principal
          , is_grant  => TRUE
          , privilege => 'connect'
         );
         COMMIT;
         DBMS_OUTPUT.put_line ('access to ' || p_acl || ' added for ' ||     p_principal);
      ELSE
         DBMS_OUTPUT.put_line (p_principal || ' already has access to ' || p_acl);
      END IF;

   END add_if_needed;
BEGIN

   EXECUTE IMMEDIATE 'grant execute on utl_mail to ' || l_principal;

   create_if_needed (p_acl => l_acl);
   add_if_needed (p_principal => l_principal, p_acl => l_acl);
   DBMS_OUTPUT.put_line ('Verification SQL:');
   DBMS_OUTPUT.put_line ('    SELECT * FROM dba_network_acls;');
   DBMS_OUTPUT.put_line ('    SELECT * FROM dba_network_acl_privileges;');
   COMMIT;
   validate_smtp_server;
END;

这篇关于使用PLSQL发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！