Estou usando o script abaixo, estava funcionando perfeitamente após troca para o servidor 'smtp.office365.com', não funciona mais:
Selecionar tudo
CREATE OR REPLACE package body frm_mail is
PROCEDURE end_mail_in_session (conn IN OUT NOCOPY UTL_SMTP.connection) IS
BEGIN
UTL_SMTP.close_data (conn);
END;
PROCEDURE end_session (conn IN OUT NOCOPY UTL_SMTP.connection) IS
BEGIN
UTL_SMTP.quit (conn);
END;
PROCEDURE end_mail (conn IN OUT NOCOPY UTL_SMTP.connection) IS
BEGIN
end_mail_in_session (conn);
end_session (conn);
END;
PROCEDURE write_text (conn IN OUT NOCOPY UTL_SMTP.connection,
MESSAGE IN VARCHAR2) IS
BEGIN
UTL_SMTP.write_data (conn, MESSAGE);
END;
PROCEDURE write_mime_header (conn IN OUT NOCOPY UTL_SMTP.connection,
NAME IN VARCHAR2,
VALUE IN VARCHAR2) IS
BEGIN
UTL_SMTP.write_raw_data (conn,UTL_RAW.cast_to_raw ( NAME
|| ': '
|| VALUE
|| UTL_TCP.crlf
)
);
END;
FUNCTION get_address (addr_list IN OUT VARCHAR2)
RETURN VARCHAR2
IS
addr VARCHAR2 (256);
i PLS_INTEGER;
FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2)
RETURN PLS_INTEGER
AS
c VARCHAR2 (5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;
BEGIN
inside_quote := FALSE;
i := 1;
len := LENGTH (str);
WHILE (i <= len)
LOOP
c := SUBSTR (str, i, 1);
IF (inside_quote)
THEN
IF (c = '"')
THEN
inside_quote := FALSE;
ELSIF (c = '\')
THEN
i := i + 1; -- Skip the quote character
END IF;
END IF;
IF (c = '"')
THEN
inside_quote := TRUE;
END IF;
IF (INSTR (chrs, c) >= 1)
THEN
RETURN i;
END IF;
i := i + 1;
END LOOP;
RETURN 0;
END;
BEGIN
addr_list := LTRIM (addr_list);
i := lookup_unquoted_char (addr_list, ',;');
IF (i >= 1)
THEN
addr := SUBSTR (addr_list, 1, i - 1);
addr_list := SUBSTR (addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;
i := lookup_unquoted_char (addr, '<');
IF (i >= 1)
THEN
addr := SUBSTR (addr, i + 1);
i := INSTR (addr, '>');
IF (i >= 1)
THEN
addr := SUBSTR (addr, 1, i - 1);
END IF;
END IF;
RETURN addr;
END;
PROCEDURE begin_mail_in_session (conn IN OUT NOCOPY UTL_SMTP.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL) IS
my_recipients VARCHAR2 (32767) := recipients;
my_sender VARCHAR2 (32767) := sender;
v_recipients varchar2(100) := '<'||recipients||'>';
v_sender varchar2(100) := '<'||sender||'>';
BEGIN
--dbms_output.put_line('ooohhh4.2.a.1'||' conn '||my_sender);
UTL_SMTP.mail (conn, '<' ||get_address (my_sender)|| '>');
WHILE (my_recipients IS NOT NULL)
LOOP
UTL_SMTP.rcpt (conn, '<' ||get_address (my_recipients)|| '>');
END LOOP;
UTL_SMTP.open_data (conn);
write_mime_header (conn, 'From', v_sender);
write_mime_header (conn, 'To', v_recipients);
write_mime_header (conn, 'Date',TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));
write_mime_header (conn, 'Subject', subject);
write_mime_header (conn, 'Content-Type', mime_type);
write_mime_header (conn, 'X-Mailer', mailer_id);
IF (priority IS NOT NULL)
THEN
write_mime_header (conn, 'X-Priority', priority);
END IF;
UTL_SMTP.write_data (conn, UTL_TCP.crlf);
IF (mime_type LIKE 'multipart/mixed%')
THEN
write_text (conn,
'This is a multi-part message in MIME format.'
|| UTL_TCP.crlf
);
END IF;
END;
PROCEDURE authentication (conn IN OUT NOCOPY
UTL_SMTP.connection,
v_user VARCHAR2,
v_password VARCHAR2) IS
BEGIN
UTL_SMTP.command (conn, 'auth login');
UTL_SMTP.command (conn, UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (v_user))));
UTL_SMTP.command (conn, UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw(v_password))));
END;
FUNCTION begin_session(PUsuario IN VARCHAR2,
PSenha IN VARCHAR2) RETURN UTL_SMTP.connection IS
conn UTL_SMTP.connection;
BEGIN
conn := UTL_SMTP.open_connection (smtp_host, 25);
UTL_SMTP.HELO (conn, smtp_domain);
IF PUsuario IS NOT NULL AND
PSenha IS NOT NULL THEN
authentication (conn,rtrim(PUsuario), PSenha);
END IF;
RETURN conn;
END;
FUNCTION begin_mail (sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL,
usuario IN VARCHAR2,
senha IN VARCHAR2)
RETURN UTL_SMTP.connection IS
conn UTL_SMTP.connection;
BEGIN
conn := begin_session(usuario,Senha);
begin_mail_in_session (conn,
sender,
recipients,
subject,
mime_type,
priority
);
RETURN conn;
END;
PROCEDURE write_boundary (conn IN OUT NOCOPY UTL_SMTP.connection,
LAST IN BOOLEAN DEFAULT
FALSE) AS
BEGIN
IF (LAST)
THEN
UTL_SMTP.write_data (conn, last_boundary);
ELSE
UTL_SMTP.write_data (conn, first_boundary);
END IF;
END;
PROCEDURE write_mb_text (conn IN OUT NOCOPY UTL_SMTP.connection,
MESSAGE IN VARCHAR2) IS
BEGIN
UTL_SMTP.write_raw_data (conn, UTL_RAW.cast_to_raw (MESSAGE));
END;
PROCEDURE write_raw (conn IN OUT NOCOPY UTL_SMTP.connection, MESSAGE IN RAW) IS
BEGIN
UTL_SMTP.write_raw_data (conn, MESSAGE);
END;
PROCEDURE begin_attachment (conn IN OUT NOCOPY UTL_SMTP.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL) IS
BEGIN
write_boundary (conn);
write_mime_header (conn, 'Content-Type', mime_type);
IF (filename IS NOT NULL)
THEN
IF (inline)
THEN
write_mime_header (conn,
'Content-Disposition',
'inline; filename="' || filename || '"'
);
ELSE
write_mime_header (conn,
'Content-Disposition',
'attachment; filename="' || filename || '"'
);
END IF;
END IF;
IF (transfer_enc IS NOT NULL)
THEN
write_mime_header (conn, 'Content-Transfer-Encoding', transfer_enc);
END IF;
UTL_SMTP.write_data (conn, UTL_TCP.crlf);
END;
PROCEDURE end_attachment (conn IN OUT NOCOPY UTL_SMTP.connection,
LAST IN BOOLEAN DEFAULT
FALSE) IS
BEGIN
UTL_SMTP.write_data (conn, UTL_TCP.crlf);
IF (LAST)
THEN
write_boundary (conn, LAST);
END IF;
END;
PROCEDURE attach_text (conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE) IS
BEGIN
begin_attachment (conn, mime_type, inline, filename);
write_text (conn, DATA);
end_attachment (conn, LAST);
END;
PROCEDURE attach_mb_text (conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE) IS
BEGIN
begin_attachment (conn, mime_type, inline, filename);
UTL_SMTP.write_raw_data (conn, UTL_RAW.cast_to_raw (DATA));
end_attachment (conn, LAST);
END;
PROCEDURE attach_base64 (conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE) IS
i PLS_INTEGER;
len PLS_INTEGER;
BEGIN
begin_attachment (conn, mime_type, inline, filename, 'base64');
i := 1;
len := UTL_RAW.LENGTH (DATA);
WHILE (i < len)
LOOP
IF (i + max_base64_line_width < len)
THEN
UTL_SMTP.write_raw_data
(conn,
UTL_ENCODE.base64_encode
(UTL_RAW.SUBSTR (DATA,
i,
max_base64_line_width
)
)
);
ELSE
UTL_SMTP.write_raw_data
(conn,
UTL_ENCODE.base64_encode (UTL_RAW.SUBSTR (DATA,
i
)
)
);
END IF;
UTL_SMTP.write_data (conn, UTL_TCP.crlf);
i := i + max_base64_line_width;
END LOOP;
end_attachment (conn, LAST);
END;
PROCEDURE ADICIONA_ANEXO (conn IN OUT NOCOPY UTL_SMTP.connection,
mime_type IN VARCHAR2 DEFAULT NULL,
directory_name IN VARCHAR2 DEFAULT NULL,
file_name IN VARCHAR2 DEFAULT NULL) IS
v_file_handle UTL_FILE.file_type;
v_line VARCHAR2(32727);
v_line_aux VARCHAR2(32727);
V_COUNT NUMBER :=0;
BEGIN
begin_attachment (conn => conn,
mime_type => mime_type,
inline => TRUE,
filename => file_name,
transfer_enc => '7 bit'
);
BEGIN
-- Open file to (r)ead and in (b)yte mode to overcome LF limitation --
v_file_handle := UTL_FILE.fopen (directory_name, file_name, 'R',32767);
LOOP
--UTL_FILE.get_raw (v_file_handle, v_rline);
--v_line := UTL_RAW.cast_to_varchar2 (v_rline);
UTL_FILE.get_line (v_file_handle, v_line, 32767);
--v_line_aux := v_line_aux||v_line;
write_text (conn => conn, MESSAGE => v_line||chr(10));
END LOOP;
END;
UTL_FILE.fclose (v_file_handle);
end_attachment (conn => conn);
END;
PROCEDURE ADICIONA_ANEXO_BINARY (conn IN OUT NOCOPY UTL_SMTP.connection,
mime_type IN VARCHAR2 DEFAULT NULL,
directory_name IN VARCHAR2 DEFAULT NULL,
file_name IN VARCHAR2 DEFAULT NULL) IS
fil BFILE;
file_len PLS_INTEGER;
max_line_width PLS_INTEGER := 54;
buf RAW (2100);
amt BINARY_INTEGER := 672 * 3;/* ensures proper format; 2016 */
filepos PLS_INTEGER := 1; /* pointer for the file */
modulo PLS_INTEGER;
chunks PLS_INTEGER;
pieces PLS_INTEGER;
data RAW (2100);
BEGIN
begin_attachment (conn => conn,
mime_type => mime_type,
inline => TRUE,
filename => file_name,
transfer_enc => 'base64'
);
BEGIN
fil := BFILENAME (directory_name, file_name);
file_len := DBMS_LOB.getlength (fil);
/*BEGIN
file_len := DBMS_LOB.getlength (fil);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Failed to send mail: Error
code'||SQLCODE||' : ERRO '||SQLERRM);
END;*/
modulo := MOD (file_len, amt);
pieces := TRUNC (file_len / amt);
IF (modulo <> 0)
THEN
pieces := pieces + 1;
END IF;
DBMS_LOB.fileopen (fil, DBMS_LOB.file_readonly);
DBMS_LOB.READ (fil, amt, filepos, buf);
DATA := NULL;
FOR i IN 1 .. pieces
LOOP
filepos := i * amt + 1;
file_len := file_len - amt;
DATA := UTL_RAW.CONCAT (DATA, buf);
chunks := TRUNC (UTL_RAW.LENGTH (DATA) / max_line_width);
IF (i <> pieces)
THEN
chunks := chunks - 1;
END IF;
write_raw (conn => conn,
MESSAGE => UTL_ENCODE.base64_encode
(DATA)
);
DATA := NULL;
IF (file_len < amt AND file_len > 0)
THEN
amt := file_len;
END IF;
DBMS_LOB.READ (fil, amt, filepos, buf);
END LOOP;
END;
end_attachment (conn => conn);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.fileclose (fil);
END;
FUNCTION converteCaracters (PIn IN VARCHAR2) RETURN VARCHAR2 IS
retorno VARCHAR2(32000) := PIn;
BEGIN
return retorno;
/*FOR NLOOP IN (SELECT *
FROM TFOCCO3I_EMAIL_CHARS
WHERE INSTR(PIn,TEXT) > 0)
LOOP
EXECUTE IMMEDIATE
'SELECT
REPLACE('''||retorno||''','''||NLOOP.TEXT||''','''||NLOOP.COD||''') FROM
DUAL' INTO retorno;
END LOOP;
RETURN retorno;*/
END;
PROCEDURE sendEmail(PRemetente IN VARCHAR2,
PDestinatario IN VARCHAR2,
PCco IN VARCHAR2,
PUsuario IN VARCHAR2,
PSenha IN VARCHAR2,
PSmtp_Server IN VARCHAR2,
PAssunto IN VARCHAR2,
PMensagem IN VARCHAR2,
PAnexos IN ANEXOSTB) IS
conn UTL_SMTP.connection;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
err_num NUMBER;
err_msg VARCHAR2 (100);
VRemetente VARCHAR2 (900) := pRemetente;
VDestinatario VARCHAR2 (900) := PDestinatario;
vcco varchar2 (900) ;
indice PLS_INTEGER;
vchar VARCHAR2 (900);
BEGIN
vcco := pcco;
smtp_host := PSmtp_Server;
smtp_domain := PSmtp_Server;
--MONTA A LINHA DO PARA PODE CONTER VARIOS DESTINATARIOS E DEVE SER FORMATADO DESTA FORMA "<EMAIL1>,<EMAIL2>"
VRemetente := rtrim(PRemetente);
--dbms_output.put_line('ooohhh3'||VRemetente);
VDestinatario := rtrim(PDestinatario);
BEGIN
conn := begin_mail
(sender => VRemetente,
recipients => VDestinatario,
subject => PAssunto,
mime_type => multipart_mime_type,
usuario => PUsuario,
senha => PSenha);
END;
--dbms_output.put_line('ooohhh5');
vchar := converteCaracters(PMensagem);
dbms_output.put_line(vchar);
BEGIN
--corpo da mensagem.
attach_text (conn => conn,
DATA => vchar|| crlf|| crlf,
mime_type => 'text/html');
END;
indice := PAnexos.first;
WHILE NOT indice IS NULL
LOOP
IF upper(PAnexos( indice ).tipo) = 'A' THEN
-- ANEXO DE ARQUIVO ASCII.
ADICIONA_ANEXO (conn=>conn,
mime_type=>PAnexos( indice ).mime_type,
directory_name=>PAnexos( indice ).directory_name,
file_name=>PAnexos( indice ).file_name);
ELSIF upper(PAnexos( indice ).tipo) = 'B' THEN
-- ANEXO DE ARQUIVO BINARIO.
ADICIONA_ANEXO_BINARY (conn=>conn,
mime_type=>PAnexos( indice ).mime_type,
directory_name=>PAnexos( indice ).directory_name,
file_name=>PAnexos( indice ).file_name);
END IF;
indice := PAnexos.NEXT( indice );
END LOOP;
--FINALIZA MENSAGEM.
end_mail (conn => conn);
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.put_line ('Error number is ' || sqlerrm);
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);
DBMS_OUTPUT.put_line ('Error number is ' || err_num);
DBMS_OUTPUT.put_line ('Error message is ' || err_msg);
end_attachment (conn => conn);
begin
utl_file.fclose_all;
exception
when others then
null;
end;
END;
PROCEDURE enviaEmail(PRemetente IN VARCHAR2,
PDestinatario IN VARCHAR2,
PCco IN VARCHAR2,
PUsuario IN VARCHAR2,
PSenha IN VARCHAR2,
PSmtp_Server IN VARCHAR2,
PAssunto IN VARCHAR2,
PMensagem IN VARCHAR2,
PAnexos IN ANEXOSTB) IS
BEGIN
sendEmail(PRemetente,
PDestinatario,
PCco ,
PUsuario ,
PSenha ,
PSmtp_Server ,
PAssunto ,
PMensagem ,
PAnexos );
END;
end;
/
