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
|
|
|
Goto Forum:
Current Time: Thu Apr 25 12:36:26 CDT 2024
|