Hi,
Im having a lot of problems when I try to send an email message using SYS.UTL_SMTP package:
my code is
create or replace
procedure P_EMAIL_DW_OPERATORS
is
type TStrings is table of varchar2(1000);
smtpSender varchar2(100) := 'sender.es@company.com';
smtpRecipient varchar2(100) := 'myemail@company.com';
mailSubject varchar2(100) := 'CRM-Alert';
smtpConn UTL_SMTP.connection;
cursor cur is
select '<tr bgcolor = ''silver'' align = ''center'' ><td>'||'OPERATOR'||'</td><td>'||
'ID_CFG_OPERATOR'||'</td><td>'||'DW_TIMESTAMP'||
'</td></tr>'
from dual
union all
select '<tr><td>'||operator||'</td><td>'||
id_cfg_operator||'</td><td>'||dw_timestamp||
'</td></tr>'
from dw_bi_operators
where to_char(dw_timestamp,'dd-mm-yy') = to_char(sysdate,'dd-mm-yy') or
to_char(dw_timestamp,'dd-mm-yy') = to_char(sysdate - 1,'dd-mm-yy');
mailBody TStrings;
BEGIN
smtpConn := UTL_SMTP.open_connection( 'Server name', port);
UTL_SMTP.helo( smtpConn, 'Server name' ); -- // this needs to be your Oracle server hostname or IP!
UTL_SMTP.command (smtpConn, 'AUTH LOGIN');
UTL_SMTP.command ( smtpConn, UTL_RAW.cast_to_varchar2 ( UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('sender.es@company.com')) ) );
UTL_SMTP.command ( smtpConn, UTL_RAW.cast_to_varchar2 ( UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('password')) ) );
UTL_SMTP.mail( smtpConn, smtpSender );
UTL_SMTP.rcpt( smtpConn, smtpRecipient );
-- can be done using an implicit cursor instead
open cur;
fetch cur bulk collect into mailBody;
close cur;
-- start sending mail body using the data command
UTL_SMTP.open_data( smtpConn );
-- write header
UTL_SMTP.write_data( smtpConn, 'MIME-Version: 1.0'||utl_tcp.CRLF );
UTL_SMTP.write_data( smtpConn, 'Content-Type: text/html'||utl_tcp.CRLF );
UTL_SMTP.write_data( smtpConn, 'From: CRM Iberia Alert'||utl_tcp.CRLF);
--||smtpSender||utl_tcp.CRLF );
UTL_SMTP.write_data( smtpConn, 'To: '||smtpRecipient||utl_tcp.CRLF );
UTL_SMTP.write_data( smtpConn, 'Subject: '||mailSubject||utl_tcp.CRLF );
-- empty line between header and rest of mail body
UTL_SMTP.write_data( smtpConn, utl_tcp.CRLF );
-- now write the HTML
UTL_SMTP.write_data( smtpConn, '<html><table border = 2>'||utl_tcp.CRLF );
for i in 1..mailBody.Count
loop
UTL_SMTP.write_data( smtpConn, mailBody(i)||utl_tcp.CRLF );
end loop;
UTL_SMTP.write_data( smtpConn, '</table></html>'||utl_tcp.CRLF );
-- close the data command
UTL_SMTP.close_data( smtpConn );
UTL_SMTP.quit( smtpConn );
end P_EMAIL_DW_OPERATORS;
and the output error is:
Conectando a la base de datos .
ORA-29279: error permanente de SMTP: 504 5.7.4 Unrecognized authentication type
ORA-06512: en "SYS.UTL_SMTP", línea 21
ORA-06512: en "SYS.UTL_SMTP", línea 99
ORA-06512: en "SYS.UTL_SMTP", línea 159
ORA-06512: en "DWARE.P_EMAIL_DW_OPERATORS", línea 33
ORA-06512: en línea 2
El proceso ha terminado.
Desconectando de la base de datos.
the system administrator said to me that the server is using an Encryption method: TLS
but I dont kow what can I do to resolve the issue
Could anyone help me
Thanks in advanced