Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29279: SMTP permanent error: 550 domain. (Oracle DB, v.11.2.0.4)
ORA-29279: SMTP permanent error: 550 domain. [message #677652] Thu, 03 October 2019 04:48 Go to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
hi all,

I followed the code below to send email via pl/sql using utl_smtp package but it is showing error,
is anything wrong or something I am missing? please check and guide.

below code copied/paste from somewhere on the net.

Create new ACL (Access Control List)
------------------------------------
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_for_mymail.xml', 
    description  => 'Create ACL for MyMail Server',
    principal    => 'HR',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

Add access point to new ACL
---------------------------
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl => 'acl_for_mymail.xml',
    host => 'smtp.mymail.com', 
    lower_port => 26,
    upper_port => NULL); 
commit;
END;
/

Add privilege
-------------
begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'acl_for_mymail.xml',
  principal => 'HR',
  is_grant  => TRUE,
  privilege => 'connect'
  );
  commit;
end;
/

ALTER SYSTEM SET smtp_out_server = 'smtp.mymail.com';

CREATE OR REPLACE FUNCTION HR.send_email(P_USERID IN VARCHAR2,
                                           P_FROM IN VARCHAR2,
                                             P_TO IN VARCHAR2, 
                                        P_SUBJECT IN VARCHAR2, 
                                            P_MSG IN VARCHAR2,
                                       P_FILENAME IN VARCHAR2)
RETURN VARCHAR2
AS
  V_MAIL_HOST VARCHAR2 (64); 
  V_PORT PLS_INTEGER;
  V_ACCOUNT VARCHAR2(500);
  V_PWD VARCHAR2(500);
  V_MAIL_CONN UTL_SMTP.CONNECTION;
  V_USER VARCHAR2(200);
  V_PASS VARCHAR2(200);
  TYPE T IS TABLE OF VARCHAR2(50);
  T_TO T := T();
  V_COUNT PLS_INTEGER :=1;
  P_MAIL_TO VARCHAR2(2000) := P_TO;
  V_TEST VARCHAR2(60);
  -- mime blocks (the sections of the email body that can become attachments)
  -- must be delimited by a string, this particular string is just an example
  c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC0123456789DE';
  v_clob                   CLOB := EMPTY_CLOB();
  v_len                    INTEGER;
  v_index                  INTEGER;
  p_ret                    VARCHAR2(400);
BEGIN
   ------ X_SERVER, X_PORT, X_ACCOUNT, CRYPTIT.DECRYPT(X_PWD)
   SELECT 'smtp.mymail.com', '26', 'oralover@mymail.com', 'pwd123' 
     INTO V_MAIL_HOST, V_PORT, V_ACCOUNT, V_PWD
     FROM DUAL;
     --FROM hr.GET_SMTP_ACCOUNT
    --WHERE X_USERID = P_USERID;
   --
   -- Build the contents before connecting to the mail server
   -- that way you can begin pumping the data immediately
   -- and not risk an SMTP timeout
   FOR x IN (SELECT *
               FROM all_objects
              WHERE ROWNUM < 20)
   LOOP
       v_clob :=
              v_clob
           || x.owner
           || ','
           || x.object_name
           || ','
           || x.object_type
           || ','
           || TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
           || UTL_TCP.crlf;
   END LOOP;
   --
   --
   LOOP
     IF (INSTR(P_MAIL_TO,',') !=0) THEN
        T_TO.EXTEND(1);
        T_TO(V_COUNT) := TRIM(SUBSTR(P_MAIL_TO,1,INSTR(P_MAIL_TO,',') -1));
        P_MAIL_TO := SUBSTR(P_MAIL_TO, INSTR(P_MAIL_TO,',') + 1, LENGTH(P_MAIL_TO) - INSTR(P_MAIL_TO,',') + 1);
        V_COUNT := V_COUNT + 1;
     ELSE
        T_TO.EXTEND(1);
        T_TO(V_COUNT) := TRIM(P_MAIL_TO);
        EXIT;
     END IF;
   END LOOP;
   --
   V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, V_PORT);
   UTL_SMTP.EHLO(V_MAIL_CONN, V_MAIL_HOST);
   -- 
   UTL_SMTP.COMMAND(V_MAIL_CONN, 'AUTH LOGIN');
   UTL_SMTP.COMMAND(V_MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(V_ACCOUNT))));
   UTL_SMTP.COMMAND(V_MAIL_CONN, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(v_pwd))));
   V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION (V_MAIL_HOST, V_PORT);
   UTL_SMTP.HELO (V_MAIL_CONN, V_MAIL_HOST);
   UTL_SMTP.MAIL (V_MAIL_CONN, P_FROM);
   -- send list of repeients ( one or more, comma seperated )
   FOR I IN 1..T_TO.LAST LOOP
       V_TEST := T_TO(I);
       UTL_SMTP.RCPT (V_MAIL_CONN, T_TO(I));
   END LOOP;
   -- 
   UTL_SMTP.OPEN_DATA (V_MAIL_CONN);
   UTL_SMTP.WRITE_DATA (V_MAIL_CONN,
   'Date: '
   || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
   || UTL_TCP.CRLF
   );
   UTL_SMTP.WRITE_DATA (V_MAIL_CONN, 'FROM: ' || P_FROM || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (V_MAIL_CONN, 'SUBJECT: ' || P_SUBJECT || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (V_MAIL_CONN, 'TO: ' || P_TO || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (V_MAIL_CONN, UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (V_MAIL_CONN, P_MSG);
   UTL_SMTP.write_data(
       V_MAIL_CONN,
       'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
   );
   UTL_SMTP.write_data(V_MAIL_CONN, UTL_TCP.crlf);
   UTL_SMTP.write_data(
       V_MAIL_CONN,
       'This is a multi-part message in MIME format.' || UTL_TCP.crlf
   );
   UTL_SMTP.write_data(V_MAIL_CONN, '--' || c_mime_boundary || UTL_TCP.crlf);
   UTL_SMTP.write_data(V_MAIL_CONN, 'Content-Type: text/plain' || UTL_TCP.crlf);\
   IF P_FILENAME IS NOT NULL THEN
     -- Set up attachment header
     UTL_SMTP.write_data(
         V_MAIL_CONN,
            'Content-Disposition: attachment; filename="'
         || P_FILENAME
         || '"'
         || UTL_TCP.crlf
     );
     UTL_SMTP.write_data(V_MAIL_CONN, UTL_TCP.crlf);
     -- Write attachment contents
     v_len := DBMS_LOB.getlength(v_clob);
     v_index := 1;
     WHILE v_index <= v_len
     LOOP
         UTL_SMTP.write_data(V_MAIL_CONN, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
         v_index := v_index + 32000;
     END LOOP;
     --
     -- End attachment
   END IF;
   UTL_SMTP.CLOSE_DATA (V_MAIL_CONN);
   UTL_SMTP.QUIT (V_MAIL_CONN);
   p_ret := 'Successfull sent email...';
   return p_ret;
EXCEPTION
    WHEN OTHERS
    THEN
        p_ret := DBMS_UTILITY.format_error_stack;
        return p_ret;
END send_email;
/

Function created.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2      v_mailsever_host VARCHAR2(30) := 'smtp.mymail.com';
  3      v_mailsever_port PLS_INTEGER  := 26;
  4      l_mail_conn  UTL_SMTP.CONNECTION;
  5      v_msg varchar2(200);
  6  BEGIN
  7      l_mail_conn := UTL_SMTP.OPEN_CONNECTION( v_mailsever_host, v_mailsever_port);
  8  EXCEPTION
  9      WHEN OTHERS THEN -- just to check - (do not want to use WHEN OTHERS)
 10      v_msg := DBMS_UTILITY.format_error_stack;
 11      DBMS_OUTPUT.PUT_LINE (v_msg);
 12* END;
SQL>
SQL> /

PL/SQL procedure successfully completed.

SQL> declare
  2    from_user varchar2(50):='MyName';
  3    from_sndr varchar2(50):='oralover@mymail.com';
  4    rcpt_to varchar2(2000):='oralover@mymail.com';
  5    msubj varchar2(80):='This is Subject';
  6    mmsg varchar2(400):= 'I am testing to send email through Oracle SQL...';
  7    ret_msg varchar2(400);
  8    begin
  9      ret_msg := HR.send_email(from_user, from_sndr, rcpt_to, msubj, mmsg, 'abcd.pdf');
 10      dbms_output.put_line(ret_msg);
 11  end;
 12  /
ORA-29279: SMTP permanent error: 550 domain.

PL/SQL procedure successfully completed.

SQL>

or there is any SMTP Server problem? I am using this configuration for my MS Outlook on same machine.

thanks.

[Updated on: Thu, 03 October 2019 04:59]

Report message to a moderator

Re: ORA-29279: SMTP permanent error: 550 domain. [message #677654 is a reply to message #677652] Thu, 03 October 2019 05:33 Go to previous messageGo to next message
John Watson
Messages: 8077
Registered: January 2010
Location: Global Village
Senior Member
You have not specified the port here

ALTER SYSTEM SET smtp_out_server = 'smtp.mymail.com';

so it will default to 25, which your ACL doers not permit. 26 is somewhat unusual, you know,
Re: ORA-29279: SMTP permanent error: 550 domain. [message #677655 is a reply to message #677654] Thu, 03 October 2019 06:04 Go to previous messageGo to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
John Watson wrote on Thu, 03 October 2019 16:03
You have not specified the port here

ALTER SYSTEM SET smtp_out_server = 'smtp.mymail.com';

so it will default to 25, which your ACL doers not permit. 26 is somewhat unusual, you know,
thanks for reply,
i have executed following with port but still same error message appears.

ALTER SYSTEM SET smtp_out_server = 'smtp.mymail.com:26';

Re: ORA-29279: SMTP permanent error: 550 domain. [message #677657 is a reply to message #677655] Thu, 03 October 2019 09:15 Go to previous messageGo to next message
Bill B
Messages: 1917
Registered: December 2004
Senior Member
Like john said, why did you use port 26, the smtp standard is port 25 and unless the smtp server at smtp.mymail.com has been configured to use port 26 it's not going to work. and you will get the error 500.
Re: ORA-29279: SMTP permanent error: 550 domain. [message #677665 is a reply to message #677657] Thu, 03 October 2019 23:46 Go to previous messageGo to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
Bill B wrote on Thu, 03 October 2019 19:45
Like john said, why did you use port 26, the smtp standard is port 25 and unless the smtp server at smtp.mymail.com has been configured to use port 26 it's not going to work. and you will get the error 500.
thank you for reply,
yes, we are using this configuration on MS Outlook ( Port 26 for SMTP Server ) and its working fine.
also I sent my output to Network department for query to service provider, may be something from their side.
will try another FREE service like MailGun to give demo to my client if it configured correctly I assume/hope this code will work fine. for this purpose I will need How To's to configure it.

thanks.

[Updated on: Thu, 03 October 2019 23:46]

Report message to a moderator

Re: ORA-29279: SMTP permanent error: 550 domain. [message #677668 is a reply to message #677665] Fri, 04 October 2019 02:31 Go to previous messageGo to next message
John Watson
Messages: 8077
Registered: January 2010
Location: Global Village
Senior Member
SMTP 550 errors are nothing to do with Oracle. Could be that you are trying to contact a bad email address (or at least, bad as far as your SMTP server is concerned). Its log should give you more information.

[Updated on: Fri, 04 October 2019 02:32]

Report message to a moderator

Re: ORA-29279: SMTP permanent error: 550 domain. [message #677672 is a reply to message #677668] Fri, 04 October 2019 05:26 Go to previous message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
John Watson wrote on Fri, 04 October 2019 13:01
SMTP 550 errors are nothing to do with Oracle. Could be that you are trying to contact a bad email address (or at least, bad as far as your SMTP server is concerned). Its log should give you more information.
already I have mentioned this in my opening post, last line:
Quote:
or there is any SMTP Server problem?
and in my reply to @Bill also mentioned that
Quote:
also I sent my output to Network department for query to service provider, may be something from their side.
hope this will resolve soon.
thanks for your passions.

[Updated on: Fri, 04 October 2019 05:47]

Report message to a moderator

Previous Topic: How do I ignore row giving error?
Next Topic: Calculation
Goto Forum:
  


Current Time: Tue Dec 10 00:36:45 CST 2019