Oracle ORA-29279: SMTP永久错误: 执行邮件过程时未实现502命令

嗨，汤姆，

早上好!

First let me make sure, here no sample table data is needed .


通常，对于所有计划的过程，我们都带有某种邮件过程，一旦作业执行结束，我们就会收到成功/失败的邮件。

我们通常使用SMTP服务器系统ip作为发送邮件过程的一部分... 在我们的代码中是这样的:

emailserver VARCHAR2 (30) :='192.168.15.115';
复制

下一步将如下，依此类推:

 l_mail_conn := UTL_SMTP.open_connection (emailserver, port);
   UTL_SMTP.helo (l_mail_conn, emailserver);
复制

我们能够成功地将邮件发送到我们公司的邮件id，但是在这种情况下，我们也需要将工作邮件发送给我们的客户。但是，当我们添加我们的客户电子邮件id时，我们无法使用上述配置设置 (smtp服务器ip :192.168.15.115)。

因此，我们考虑用以下代码片段替换SMTP服务器IP(192.168.15.115):

  emailserver                VARCHAR2 (30)       := 'smtp.gmail.com';
   port                       NUMBER              := 25;
 l_mail_conn := UTL_SMTP.open_connection (emailserver, port);
   UTL_SMTP.helo (l_mail_conn, emailserver);
复制

我们收到以下错误消息:

Error starting at line 1 in command:
execute PRC_RECON_INVLOC_VARIANCE_OFF
Error report:
ORA-29279: SMTP permanent error: 502 Command not implemented
ORA-06512: at "SYS.UTL_SMTP", line 54
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 289
ORA-06512: at "QCHKSLOC_JAN1919.PRC_RECON_INVLOC_VARIANCE_OFF", line 48
ORA-06512: at line 1
29279. 00000 -  "SMTP permanent error: %s"
*Cause:    A SMTP permanent error occurred.
*Action:   Correct the error and retry the SMTP operation.
复制

任何解决上述问题的建议将不胜感激。

请让我们知道系统管理员是否需要做任何事情。

以下是我们一直在使用的完整代码集:

CREATE OR REPLACE PROCEDURE prc_recon_invloc_variance_off
AS
   mailcontent                VARCHAR2 (500);
   sendoraddress              VARCHAR2 (30)       := 'GL_RECON@xyz.net';
   v_recipient1               VARCHAR2 (30)       := 'praveenkumar.k@xyz.net';
   v_recipient3               VARCHAR2 (30)       := 'muneer@xyz.net';
   -- emailserver VARCHAR2 (30) :='smtp.gmail.com';--'192.168.15.115';
   emailserver                VARCHAR2 (30)       := 'smtp.gmail.com';
   port                       NUMBER              := 587;
   l_mail_conn                UTL_SMTP.connection;
   c_mail_boundary   CONSTANT VARCHAR2 (255)      DEFAULT '10000000000000';
   crlf                       VARCHAR2 (2)        := CHR (13) || CHR (10);
   v_process_name             VARCHAR2 (100):= 'PRC_RECON_INVLOC_VARIANCE_OFF';
   v_sqlerrorcd               VARCHAR2 (10);
   v_sqlerrormsg              VARCHAR2 (255);
   v_cnt                      NUMBER;
   file_name                  VARCHAR2 (50);
   v_user_name                VARCHAR2 (20);
   p_username                 VARCHAR2 (100)      := 'gl_recon@xyz.net';
   p_password                 VARCHAR2 (20)       := 'abc@123';
------------------------------------------------------
BEGIN
   SELECT SYS_CONTEXT ('USERENV', 'current_schema')
     INTO v_user_name
     FROM DUAL;

   l_mail_conn := UTL_SMTP.open_connection (emailserver, port);
   UTL_SMTP.helo (l_mail_conn, emailserver);
   UTL_SMTP.starttls (l_mail_conn);
   --------------BEGIN AUTHENTICATION--------------
   UTL_SMTP.command (l_mail_conn, 'AUTH LOGIN');
   /*  UTL_SMTP.command (l_mail_conn,
                       UTL_ENCODE.text_encode (p_username,
                                               'WE8ISO8859P1',
                                               UTL_ENCODE.base64
                                              )
                      );
     UTL_SMTP.command (l_mail_conn,
                       UTL_ENCODE.text_encode (p_password,
                                               'WE8ISO8859P1',
                                               UTL_ENCODE.base64
                                              )
                      );*/
   UTL_SMTP.command (l_mail_conn,
                     UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_username)
                                              )
                    );
   UTL_SMTP.command (l_mail_conn,
                     UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_password)
                                              )
                    );
   --------------END AUTHENTICATION--------------
   UTL_SMTP.mail (l_mail_conn, sendoraddress);
   UTL_SMTP.rcpt (l_mail_conn, v_recipient1);
   UTL_SMTP.rcpt (l_mail_conn, v_recipient3);
   UTL_SMTP.open_data (l_mail_conn);
   UTL_SMTP.write_data (l_mail_conn,
                           'Date: '
                        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                        || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (l_mail_conn, 'To: ' || v_recipient1 || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'Bcc: ' || v_recipient3 || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn,
                        'From: ' || sendoraddress || UTL_TCP.crlf);
   UTL_SMTP.write_data
         (l_mail_conn,
             'Subject: Success status For Daily Recon Inventory LOC Report On'
          || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
          || UTL_TCP.crlf
         );
   UTL_SMTP.write_data (l_mail_conn,
                        'Reply-To: ' || sendoraddress || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn,
                           'Content-Type: multipart/mixed; boundary="'
                        || c_mail_boundary
                        || '"'
                        || UTL_TCP.crlf
                        || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (l_mail_conn, '--' || c_mail_boundary || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn,
                           'Content-Type: text/html'
                        || UTL_TCP.crlf
                        || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (l_mail_conn,
                        '' || UTL_TCP.crlf || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (l_mail_conn,
                        'Hi,' || UTL_TCP.crlf || ' ' || CHR (13) || '' || crlf
                       );
   UTL_SMTP.write_data (l_mail_conn, '

' || crlf);
   UTL_SMTP.write_data
             (l_mail_conn,
                 ' Today No Variance For Daily Recon Inventory LOC Report On '
              || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
              || ' @ '
              || v_user_name
              || ' '
              || 'DB.'
             );
   UTL_SMTP.write_data
      (l_mail_conn,
       '


Thanks and Regards ,
                                
 DB Development.
                                
                                '
      );
   UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   UTL_SMTP.close_data (l_mail_conn);
   UTL_SMTP.quit (l_mail_conn);
END;
复制

问候，
维内什

502错误来自邮件服务器。所以你需要找出为什么邮件服务器 (gmail) 发送这个错误。

这不在我的范围内。但是我的猜测是您的SSL证书设置不正确。

您可以在以下位置找到如何将UTL_SMTP与SSL一起使用的工作示例:

http://oracle.ninja/sending-secure-e-mails-out-of-the-database-ssltls-utl_smtp-openssl-acl-wallet/