Skip to content

Instantly share code, notes, and snippets.

@felix-orduz

felix-orduz/1.email_simple.sql

Last active February 12, 2016 22:10
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save felix-orduz/7156133 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save felix-orduz/7156133 to your computer and use it in GitHub Desktop.
Download ZIP
Envio Email Con PL/SQL
Raw
1.email_simple.sql
CREATE OR REPLACE PROCEDURE CEM.send_mail(
sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2
)IS
crlf VARCHAR2(2):= UTL_TCP.CRLF;
connection utl_smtp.connection;
mailhost VARCHAR2(30) := 'host';
BEGIN
-- Inicio Conexion
connection := utl_smtp.open_connection(mailhost,26);
utl_smtp.ehlo(connection, mailhost);
--authentication
utl_smtp.command( connection, 'AUTH LOGIN');
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'email' ))) );
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'password' ))) );
-- Handshake with the SMTP server
utl_smtp.mail(connection, sender);
utl_smtp.rcpt(connection, recipient);
UTL_SMTP.data(connection, message || UTL_TCP.crlf || UTL_TCP.crlf);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
END;
Raw
2.email_multilinea.sql
CREATE OR REPLACE PROCEDURE CEM.send_mail(
sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2
)IS
crlf VARCHAR2(2):= UTL_TCP.CRLF;
connection utl_smtp.connection;
mailhost VARCHAR2(30) := 'host';
header VARCHAR2(1000);
BEGIN
-- Start the connection.
connection := utl_smtp.open_connection(mailhost,26);
utl_smtp.ehlo(connection, mailhost);
--authentication
utl_smtp.command( connection, 'AUTH LOGIN');
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'email' ))) );
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'password' ))) );
utl_smtp.mail(connection, sender);
utl_smtp.rcpt(connection, recipient);
-- Handshake with the SMTP server
utl_smtp.open_data(connection);
--escribe la cabecera
utl_smtp.write_data(connection, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
utl_smtp.write_data(connection, 'To: ' || recipient || UTL_TCP.crlf);
utl_smtp.write_data(connection, 'From: ' || sender || UTL_TCP.crlf);
utl_smtp.write_data(connection, 'Subject: ' || subject || UTL_TCP.crlf);
utl_smtp.write_data(connection, 'Reply-To: ' || sender || UTL_TCP.crlf || UTL_TCP.crlf);
--escribe el mensaje
utl_smtp.write_data(connection, message || UTL_TCP.crlf || UTL_TCP.crlf);
utl_smtp.close_data(connection);
utl_smtp.quit(connection);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
END;
Raw
3.email_html.sql
CREATE OR REPLACE PROCEDURE CEM.send_mail(
sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
html_message IN VARCHAR2
)IS
crlf VARCHAR2(2):= UTL_TCP.CRLF;
connection utl_smtp.connection;
mailhost VARCHAR2(30) := 'host';
header VARCHAR2(1000);
BEGIN
-- Start the connection.
connection := utl_smtp.open_connection(mailhost,26);
utl_smtp.ehlo(connection, mailhost);
--authentication
utl_smtp.command( connection, 'AUTH LOGIN');
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'email' ))) );
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'password' ))) );
utl_smtp.mail(connection, sender);
utl_smtp.rcpt(connection, recipient);
-- Handshake with the SMTP server
utl_smtp.open_data(connection);
--cabecera
header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
'From: '||sender||''||crlf||
'Subject: '||subject||crlf||
'To: '||recipient||crlf||
'CC: '||ccrecipient||crlf||
'MIME-Version: 1.0'||crlf;
utl_smtp.write_data(connection, header);
--escribe el mensaje
IF message IS NOT NULL THEN
UTL_SMTP.write_data(connection, 'Content-Type: text/plain; charset="UTF-8"' || crlf || crlf);
UTL_SMTP.write_data(connection, message);
UTL_SMTP.write_data(connection, crlf || crlf);
END IF;
IF html_message IS NOT NULL THEN
UTL_SMTP.write_data(connection, 'Content-Type: text/html; charset="UTF-8"' || crlf || crlf);
UTL_SMTP.write_data(connection, html_message);
UTL_SMTP.write_data(connection, crlf || crlf);
END IF;
utl_smtp.write_data(connection, '--' || crlf || crlf);
utl_smtp.close_data(connection);
utl_smtp.quit(connection);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
END;
Raw
4.email_adjuntos.sql
CREATE OR REPLACE PROCEDURE CEM.send_mail(
sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
html_message IN VARCHAR2
)IS
crlf VARCHAR2(2):= UTL_TCP.CRLF;
connection utl_smtp.connection;
mailhost VARCHAR2(30) := 'mail.cemcolombia.co';
header VARCHAR2(1000);
v_src_loc BFILE;
v_amount INTEGER;
l_blob BLOB;
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
v_src_loc := BFILENAME('CEM', 'Desert.jpg');
DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount);
-- Start the connection.
connection := utl_smtp.open_connection(mailhost,26);
utl_smtp.ehlo(connection, mailhost);
--authentication
utl_smtp.command( connection, 'AUTH LOGIN');
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'administradorti@cemcolombia.co' ))) );
utl_smtp.command( connection, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Test.2013*' ))) );
utl_smtp.mail(connection, sender);
utl_smtp.rcpt(connection, recipient);
-- Handshake with the SMTP server
utl_smtp.open_data(connection);
--cabecera
header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
'From: '||sender||''||crlf||
'Subject: '||subject||crlf||
'To: '||recipient||crlf||
'CC: '||ccrecipient||crlf||
'MIME-Version: 1.0'||crlf;
utl_smtp.write_data(connection, header);
UTL_SMTP.WRITE_DATA(connection,'Content-Type: multipart/mixed; ' || crlf);
UTL_SMTP.WRITE_DATA(connection,' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||crlf);
UTL_SMTP.WRITE_DATA(connection, crlf);
--escribe el mensaje
IF message IS NOT NULL THEN
UTL_SMTP.WRITE_DATA(connection,'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.write_data(connection,'Content-Type: text/plain; charset="UTF-8";' || crlf || crlf);
UTL_SMTP.write_data(connection,message);
UTL_SMTP.write_data(connection,crlf || crlf);
END IF;
IF html_message IS NOT NULL THEN
UTL_SMTP.write_data(connection, 'Content-Type: text/html; charset="UTF-8"' || crlf || crlf);
UTL_SMTP.write_data(connection, html_message);
UTL_SMTP.write_data(connection, crlf || crlf);
END IF;
--adjunto
UTL_SMTP.WRITE_DATA(connection,'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(connection,'Content-Type: application/octet-stream' || UTL_TCP.CRLF);
UTL_SMTP.write_data(connection, 'Content-Type: application/octet-stream' || '; name="prueba.jpg' || '"' || crlf);
UTL_SMTP.write_data(connection, 'Content-Transfer-Encoding: base64' || crlf);
UTL_SMTP.write_data(connection, 'Content-Disposition: attachment; filename="prueba.jpg' || '"' || crlf || crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(connection, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(l_blob, l_step, i * l_step + 1))));
END LOOP;
UTL_SMTP.write_data(connection, UTL_TCP.crlf || UTL_TCP.crlf);
utl_smtp.write_data(connection, '--' || crlf || crlf);
utl_smtp.close_data(connection);
utl_smtp.quit(connection);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
END;
Raw
Leeme
Envio de Correo Electronico Con Oracle 10g (UTL_SMTP)
El paquete UTL_SMTP fue introducido con Oracle 8i y puede ser usado para enviar correos desde PL/SQL, la conexiones SSL no son soportadas nativamente por tal razon la conexion con GMAIL no es soportada.
*) Email Simples
es un email simple que se conecta por un puerto de smtp
no se autentica, ademas solo se puede enviar 1 linea en el texto
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
*) Email Simple con autenticación
por defecto no existe una funcion para autenticar el usuario y contraseña hay que negociar con el servidor smtp la utenticacion.
Ver Ejemplo 1.email_simple.sql
*) Email Multilinea con autenticacion
se utilza la funcion write_data del paquete UTL_SMTP para poder enviar mensajes multilinea
ver ejemplo 2.email_multilinea.sql
*) Email Formato HTML
es basicamente el mismo ejemplo que el email multilinea pero con cabeceras de tipo HTML.
ver ejemplo 3.mail.html.sql
*) Email Adjunto
se debe utlizar un elemento blob donde se tendra el archivo a enviar, en el ejemplo se lee un archivo de un directorio
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment