I’m getting an ORA-29279 error when running the below snipped. Suggest me if some issue in this
CREATE OR REPLACE PROCEDURE CPO.fsc_temp_MAIL (l_from IN VARCHAR2,
l_to IN VARCHAR2,
Subject IN VARCHAR2,
Mesg IN VARCHAR2,
Cc IN VARCHAR2 default null,
P_Html BOOLEAN := FALSE) IS
l_to1 VARCHAR2(32000) := l_to;
Mhost VARCHAR2(64) := '192.168.0.6';
crlf varchar2(2) := CHR(13) || CHR(10);
conn UTL_SMTP.connection;
Address varchar2(32700);
BEGIN
conn := UTL_SMTP.open_connection(Mhost,25);
UTL_SMTP.helo(conn, Mhost);
UTL_SMTP.mail(conn, l_from);
GET_TEMP_INFO_MAIL(conn,l_to1);
If Cc is not null then
GET_TEMP_INFO_MAIL(conn,Cc);
end if;
IF P_Html THEN
Address := 'Date: ' || TO_CHAR(SYSDATE, 'DD MON RRRR HH24:MI:SS') ||
crlf ||'From: ' || l_from ||
crlf ||'To: ' || l_to ||
crlf ||'Cc: ' || Cc ||
crlf ||'Subject: ' || Subject || crlf
|| 'Content-Type: text/html; charset=us-ascii' || crlf
|| 'Content-Transfer-Encoding: 7bit' || crlf
|| '<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">' || crlf
|| '<html>' || crlf
|| '<head>' || crlf
|| '<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">' || crlf
|| '<title>' || subject || '</title>' || crlf
|| '</head>' || crlf
|| '<body>' || crlf|| utl_tcp.crlf
|| mesg || crlf
|| '</body></html>';
ELSE
Address := 'Date: ' || TO_CHAR(SYSDATE, 'DD MON RRRR HH24:MI:SS') ||
crlf ||'From: ' || l_from ||
crlf ||'To: ' || l_to ||
crlf ||'Cc: ' || Cc ||
crlf ||'Subject: ' || Subject ||
crlf || utl_tcp.crlf || mesg;
END IF;
UTL_SMTP.data(conn, Address);
UTL_SMTP.quit(conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
when I execute that procedure
execute fsc_temp_MAIL('usmanafb@ctm.com.pk','abc@ctm.com.pk','test for subject ','sdf','xyz@ctm.com.pk',True);
ORA-29279: SMTP permanent error: 530 5.7.1 Client was not authenticated
don’t know how to deal with this
this is some kind of smtp setting ? all email address is valid
if some one have better solution then tell me I create these pocedure in plsql
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
A quick note in case one is getting the following exception while using APEX_MAIL.SEND procedure.
ORA-29279: SMTP permanent error: 554 5.7.1 : Relay access denied
ORA-29279 is a sort of catch-all exception for a whole class of SMTP errors, so the SMTP error code (in this case 554) followed by the textual description is what really matters. Some time ago i wrote another blog posting for a different situation where ORA-29279 was returned.
In this case the 554 Relay access denied error was caused by the setup of POSTFIX on one virtual machine acting as a test SMTP server, which was not configured to accept mail relaying from IP addresses other than the local machine (127.0.0.1/8).
After adding the IP address of the database server running Oracle Application Express to the list of «trusted» addresses, i could eventually send the emails stuck in the queue.
In case you are dealing with POSTFIX too, the relevant parameter is mynetworks.
You can read more about the setting of this parameter (and associated ones) in the POSTFIX documentation.
See message translations for ORA-29279 and search additional resources.
How to Send an Email Using UTL_SMTP with Authenticated Mail Server. (Doc ID 885522.1)
GOAL
The UTL_SMTP package is designed for sending electronic mail (E-Mail) over simple mail transfer protocol (SMTP) as specified by RFC821. Some mail servers require username and password to be supplied for authentication. If the username and password is not supplied when the SMTP server expects it, then call to the UTL_SMTP will fail with «ORA-29279: SMTP permanent error: 530 Authentication required«.
The sample code below shows how to include the username/password for the mail server.
SOLUTION
create or replace procedure
testmail
(
fromm varchar2,
too varchar2,
sub varchar2,
body varchar2,
port number
)
is
objConnection utl_smtp.connection;
vrData varchar2(32000);
username varchar2(20) := ‘<username>’;
password varchar2(20) := ‘<password>’;
begin
objConnection := utl_smtp.open_connection(‘<your domain server name>’,port);
utl_smtp.helo(objConnection, ‘<your domain name server>’);
utl_smtp.command(objConnection, ‘AUTH LOGIN’);
utl_smtp.command(objConnection,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(username))));
utl_smtp.command(objConnection,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(password))));
utl_smtp.mail(objConnection, fromm);
utl_smtp.rcpt(objConnection, too);
utl_smtp.open_data(objConnection);
/*** Sending the header information */
utl_smtp.write_data(objConnection, ‘From: ‘||fromm || utl_tcp.crlf);
utl_smtp.write_data(objConnection, ‘To: ‘||too || utl_tcp.crlf);
utl_smtp.write_data(objConnection, ‘Subject: ‘ || sub || utl_tcp.crlf);
utl_smtp.write_data(objConnection, ‘MIME-Version: ‘ || ‘1.0’ || utl_tcp.crlf);
utl_smtp.write_data(objConnection, ‘Content-Type: ‘ || ‘text/html;’);
utl_smtp.write_data(objConnection, ‘Content-Transfer-Encoding: ‘ || ‘»8Bit»‘ ||utl_tcp.crlf);
utl_smtp.write_data(objConnection,utl_tcp.crlf);
utl_smtp.write_data(objConnection,utl_tcp.crlf||'<HTML>’);
utl_smtp.write_data(objConnection,utl_tcp.crlf||'<BODY>’);
utl_smtp.write_data(objConnection,utl_tcp.crlf||'<FONT COLOR=»red» FACE=»Courier New»>’||body||'</FONT>’);
utl_smtp.write_data(objConnection,utl_tcp.crlf||'</BODY>’);
utl_smtp.write_data(objConnection,utl_tcp.crlf||'</HTML>’);
utl_smtp.close_data(objConnection);
utl_smtp.quit(objConnection);
exception
when utl_smtp.transient_error OR utl_smtp.permanent_error then
utl_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
when others then
utl_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
end testmail;
/
Call the procedure by replacing the values in angular bracket as appropriately.
declare
vdate Varchar2(25);
begin
vdate := to_char(sysdate,’dd-mon-yyyy HH:MI:SS AM’);
testmail(‘<sender_mailid>’, ‘<recipient_mailid>’, ‘<subject_line>’,’This is a UTL_SMTP-generated email at ‘|| vdate,25);
end;
/
I was trying to use utl_smtp to send mails from Oracle database to myself.
This is the PLSQL code I used to do it:
declare
c utl_smtp.connection;
msg_from varchar2(50) := ‘Oracle9.2’;
mailhost VARCHAR2(30) := ‘mail server’;
dbserver VARCHAR2(30) := ‘db server’;
from_eml VARCHAR2(40) := ‘from email address’;
to_eml VARCHAR2(40) := ‘to email address’;
begin
c := utl_smtp.open_connection(mailhost, 25); — SMTP on port 25
utl_smtp.helo(c, dbserver);
utl_smtp.mail(c, from_eml);
utl_smtp.rcpt(c, to_eml‘);
UTL_SMTP.open_data (c);
utl_smtp.write_data(c, ‘Subject: Your Subject Line Here’);
UTL_SMTP.write_data (c, ‘This is test message’);
UTL_SMTP.close_data (c);
UTL_SMTP.quit (c);
END;
However, when running the above block, it was giving the following error:
ORA-29279: SMTP permanent error: 530 5.7.1 Client was not authenticated
ORA-06512: at «SYS.UTL_SMTP», line 20
ORA-06512: at «SYS.UTL_SMTP», line 98
ORA-06512: at «SYS.UTL_SMTP», line 221
It seems like that the exchange server was not relaying the IP address for the database server to send emails.
To fix it, the system admin managing the exchange added the database server IP address to relay server list.
And it all started to work then.