Fórum Enviar e-mail pelo Oracle #28385

Ex:

Procedure SEND_EMAIL:

CREATE OR REPLACE
PROCEDURE ´SEND_EMAIL´ (from_address IN VARCHAR2,
to_address IN VARCHAR2,
subject IN VARCHAR2,
body IN VARCHAR2)
is
c utl_smtp.connection;
l_pos NUMBER := 1;
l_separators VARCHAR2(1) := ´,´;
recipient VARCHAR2(200);

-- set these for your environment
v_smtp_server VARCHAR2(100) := ´smtpout.cobox.com.br´;
v_smtp_domain VARCHAR2(80) := ´cobox.com.br´;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ´: ´ || header || utl_tcp.CRLF);
END;

BEGIN
c := utl_smtp.open_connection(v_smtp_server);
utl_smtp.helo(c, v_smtp_domain);
utl_smtp.mail(c, from_address);

-- use get token to get all to recipients (separated by commas)
LOOP
recipient := mdw_util.Get_Token(to_address, l_pos, l_separators);

IF recipient IS NULL THEN
EXIT;
ELSE
utl_smtp.rcpt(c, recipient);
END IF;
END LOOP;
-- CC utl_smtp.rcpt( conn, ´cc:user1@us.oracle.com´ ); have to loop through these too
-- BCC utl_smtp.rcpt( conn, ´bcc:user1@us.oracle.com´ ); have to loop through these too
utl_smtp.open_data(c);
send_header(´From´, ´´´ || from_address || ´´ <´ || from_address || ´>´);
send_header(´To´, ´´´ || to_address || ´´ <´ || translate(to_address,´,´,´ ´) || ´>´);
send_header(´Subject´, subject);

--When assembling the body, RFC 821 requires that the lines to be terminated
-- by <CR><LF> which is a character 13 and character 10 (accomplished in
-- PL/SQL by using: CHR(13)||CHAR(10)).

utl_smtp.write_data(c, utl_tcp.CRLF || body || utl_tcp.CRLF );
utl_smtp.close_data(c);
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(c);
raise_application_error(-20000,
´Failed to send mail due to the following error: ´ || sqlerrm);
END;

depois execute a procedure:

begin
exec send_email(´EMAIL DE ENVIO´, ´EMAIL DA PESSOA´,
´Aula de hoje´, ´Queridos alunos, hoje apartir das 10:00 horas nπo hßvera aula.´ );
end;

Espero ter ajudado.