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 |
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 #677655 is a reply to message #677654] |
Thu, 03 October 2019 06:04 |
oralover2006
Messages: 144 Registered: January 2010 Location: India
|
Senior Member |
|
|
John Watson wrote on Thu, 03 October 2019 16:03You 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 #677665 is a reply to message #677657] |
Thu, 03 October 2019 23:46 |
oralover2006
Messages: 144 Registered: January 2010 Location: India
|
Senior Member |
|
|
Bill B wrote on Thu, 03 October 2019 19:45Like 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 #677672 is a reply to message #677668] |
Fri, 04 October 2019 05:26 |
oralover2006
Messages: 144 Registered: January 2010 Location: India
|
Senior Member |
|
|
John Watson wrote on Fri, 04 October 2019 13:01SMTP 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
|
|
|
Goto Forum:
Current Time: Sat Jun 08 03:04:57 CDT 2024
|