Oracle - Adjuntar archivos

Hola a todos.
Tengo un disparador creado en la base de datos que envia un e-mail cuando se produce un update de una tabla. Funciona correctamente pero me gustaría incluir la posibilidad de adjuntar archivos y no se hacerlo, por favor ¿alguien me podría ayudar?
Muchas gracias.
Saludos

Hola marta,

Mira a qui te envio el proceso que yo utilzo para enviar el mail con archivos atachados. este proces se basa en un package que esta en internet y en alguna parte de este foro en la seccion de oracle el package se llama DEMO MAIL, solo es una pequeña aclaracion.

CREATE OR REPLACE PROCEDURE SEND_MAIL_BUENO( X_DE IN VARCHAR2 ,
X_PARA IN VARCHAR2,
X_ASUNTO IN VARCHAR2 ,
X_NOM_ARCHIVO IN VARCHAR2 ,
X_TIPO_ARCHIVO IN VARCHAR2 ,
X_MENSAJE IN VARCHAR2 DEFAULT NULL,
X_cPath IN VARCHAR2
) IS

conn utl_smtp.connection;
data RAW(2000);
v_cPath varchar2 (50) := 'MY_FILES';
bfile_handle bfile;
bfile_len number;
pos number;
read_bytes number;
mensaje varchar2(2000) := 'Este correo contiene los datos de los asegurados con la cobertura de asistencia.

Correo fue generado automáticamente por:



FIRMA DEL
REMITENTE


Nota: los programas de correo electrónico pueden impedir el envío y la recepción de cierto tipo de archivos adjuntos para proteger su equipo frente a posibles virus. Compruebe la configuración de seguridad de su correo electrónico para determinar cómo se controlarán los documentos adjuntos.'
;

BEGIN
if X_PARA is null then
raise_application_error(-20501,'ATTACH el nombre del destinatario no puede estar en blanco '||SQLERRM);
end if;
conn := demo_mail.begin_mail(
sender => X_DE,
recipients => X_PARA,
subject => X_ASUNTO||' '||TRUNC(SYSDATE) ,
mime_type => demo_mail.MULTIPART_MIME_TYPE);

----cuerpo del Correo
demo_mail.begin_attachment(conn, 'text/plain',TRUE,NULL);
demo_mail.write_text(conn,NVL(X_MENSAJE ,mensaje));
demo_mail.end_attachment(conn, FALSE);

--INICIALIZA EL ATACH
demo_mail.begin_attachment( conn => conn,
mime_type => X_TIPO_ARCHIVO,
inline => TRUE,
filename => X_NOM_ARCHIVO,
transfer_enc => 'base64'
);

-- ABRE EL ARCHIVO LOB
bfile_handle := bfilename( nvl(X_cPath,v_cPath),X_NOM_ARCHIVO );
bfile_len := dbms_lob.getlength(bfile_handle);
pos := 1;
dbms_lob.open(bfile_handle,dbms_lob.lob_readonly);
loop
BEGIN
if pos + 57 - 1 > bfile_len then
read_bytes := bfile_len - pos + 1;
else
read_bytes := 57;
end if;
dbms_lob.read(bfile_handle,read_bytes,pos,data);
demo_mail.write_raw( conn => conn, message => utl_encode.base64_encode(data));
pos := pos + 57;
if pos > bfile_len then
exit;
end if;
exception
when no_data_found then
EXIT;
when others then
raise_application_error(-20501,'ATTACH '||SQLERRM);
END;
end loop;
dbms_lob.close(bfile_handle);
demo_mail.end_attachment( conn => conn );

demo_mail.end_mail( conn => conn );

END;

Espero que te sirva byyy

de donde se puede bajar el procedure demo_mail?. si lo tenes, me lo podes pasar?
Gracias

Vale, este es el link http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/maildemo_sql.txt

Muchas gracias!

Muy buen ejemplo!!!

Era justo lo que estaba buscando.

Ahora tengo una duda. Dentro del procedimiento he agragado los parámatros de 'Cc', pero actualmente solo envía a la persona que está en el campo 'Para:' y a los que están con copia no envía.

Hice pruebas enviando al hotmail, al yahoo, al gmail, al correo de mi trabajo y definitivamente no llega.

Te adjunto lo que tengo.

Saludos

Danilo

PD1 : La respuesta por fa a mi correo [email protected]
PD2 : Los comentarios los puedes ver en el parámetro 'cc'

Te adjunto en varios mails el script que lo modifique

----------------------------------------

CREATE OR REPLACE PACKAGE demo_mail IS

----------------------- Customizable Section -----------------------
--Customize the SMTP host, port and your domain name below.
smtp_host VARCHAR2(256) := 'server_correo.dominio.com';

smtp_port PLS_INTEGER := 25;

smtp_domain VARCHAR2(256) := 'dominio.com';

-- Customize the signature that will appear in the email's MIME header.
-- Useful for versioning.
mailer_id CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP';

---------------------END customizable section ---------------------
-- A unique string that demarcates boundaries of parts in a multi-part email
-- The string should not appear inside the body of any part of the email.
-- Customize this if needed or generate this randomly dynamically.
boundary CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

first_boundary CONSTANT VARCHAR2(256) := '--' || boundary || utl_tcp.crlf;

last_boundary CONSTANT VARCHAR2(256) := '--' || boundary || '--' ||
utl_tcp.crlf;

--a mime TYPE that denotes multi - part email(mime) messages.
multipart_mime_type CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="' ||
boundary || '"';

max_base64_line_width CONSTANT PLS_INTEGER := 76 / 4 * 3;

-- A simple email API for sending email IN plain text IN a single CALL.
-- The format of an email address is one of these:
-- someone@some-domain
-- "Someone at some domain" someone@some-domain
-- someone at some domain someone@some-domain
-- The recipients is a list of email addresses separated by
-- either a ", " or a "; "
PROCEDURE mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2, -- ** ESTA PARTE ES PARA EL CAMPO "Cc" **
subject IN VARCHAR2,
message IN VARCHAR2);

-- Extended email API to send email in HTML or plain text with no size limit.
-- first, begin the email by begin_mail(). then, call write_text() repeatedly
-- to send email in ascii piece-by-piece. or, call write_mb_text() to send
-- email in non-ascii or multi-byte character set. end the email with
-- end_mail().
FUNCTION begin_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2, -- ** ESTA PARTE ES PARA EL CAMPO "Cc" **
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection;

-- write email body in ascii
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);

-- write email body in non-ascii (including multi-byte). the email body
-- will be sent in the database character set.
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);

-- write email body in binary
PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
message IN RAW);

-- apis to send email with attachments. Attachments are sent by sending
-- emails in "multipart/mixed" MIME format. specify that mime format WHEN
-- beginning an email with begin_mail().
-- send a single text attachment.
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);

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);

-- send a binary attachment. the attachment will be encoded IN base - 64
-- encoding format.
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);

-- send an attachment with no size limit. first, begin the attachment
-- with begin_attachment(). THEN, CALL write_text repeatedly TO send
-- the attachment piece-by-piece. if the attachment is text-based but
-- in non-ascii OR multi - BYTE CHARACTER SET, USE write_mb_text() INSTEAD.
-- to send binary attachment, the binary content should first be
-- encoded IN base - 64 encoding format USING THE demo PACKAGE FOR 8i,
-- or the native one in 9i. end the attachment with end_attachment.
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);

-- end the attachment.
PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
LAST IN BOOLEAN DEFAULT FALSE);

-- end the email.
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

-- extended email api to send multiple emails in a session FOR better
-- performance. first, begin an email session with begin_session.
-- then, begin each email with a session by CALLING begin_mail_in_session
-- instead of begin_mail. end the email with end_mail_in_session instead
-- of end_mail. end THE email session BY end_session.
FUNCTION begin_session RETURN utl_smtp.connection;

-- begin an email in a session.
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2, -- ** ESTA PARTE ES PARA EL CAMPO "Cc" **
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL);

-- end an email IN a session.
PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);

-- end an email session.
PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

END;
/

------------------------------------------------------------------------
PROCEDURE mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2) IS
conn utl_smtp.connection;
BEGIN
conn := begin_mail(sender, recipients, cc, subject);
write_text(conn, message);
end_mail(conn);
END;

------------------------------------------------------------------------
FUNCTION begin_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
conn := begin_session;
begin_mail_in_session(conn,
sender,
recipients,
cc,
subject,
mime_type,
priority);
RETURN 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_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 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));
-- write_text(conn, 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');
-- Split the Base64-encoded attachment into multiple lines
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 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 end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
end_mail_in_session(conn);
end_session(conn);
END;

------------------------------------------------------------------------
FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
RETURN conn;
END;

------------------------------------------------------------------------
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
cc 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;
BEGIN
-- Specify sender's address (our server allows bogus address
-- as long as it is a full email address ([email protected]).
utl_smtp.mail(conn, get_address(my_sender));
--Specify recipient(s) of the email.
WHILE (my_recipients IS NOT NULL) LOOP
utl_smtp.rcpt(conn, get_address(my_recipients));
END LOOP;
-- Start body of email
utl_smtp.open_data(conn);
-- Set "From" MIME header
write_mime_header(conn, 'From', sender);
-- Set "To" MIME header
write_mime_header(conn, 'To', recipients);
---------------------------------------------------------------------------------------------
-- Esta parte se agrega para que tome el parámetro Cc "Con copia"
---------------------------------------------------------------------------------------------
-- Set "Con Copia a" MIME header
write_mime_header(conn, 'Cc', cc);
---------------------------------------------------------------------------------------------
-- Esta parte se agrega para que tome el parámetro Cc "Con copia"
---------------------------------------------------------------------------------------------
-- Set "Subject" MIME header
write_mime_header(conn, 'Subject', subject);
-- Set "Content-Type" MIME header
write_mime_header(conn, 'Content-Type', mime_type);
-- Set "X-Mailer" MIME header
write_mime_header(conn, 'X-Mailer', mailer_id);
-- Set priority:
-- High Normal Low
-- 1 2 3 4 5
IF (priority IS NOT NULL) THEN
write_mime_header(conn, 'X-Priority', priority);
END IF;
-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
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 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;

END;
/

------------------------------------------------------------------------
PROCEDURE mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2) IS
conn utl_smtp.connection;
BEGIN
conn := begin_mail(sender, recipients, cc, subject);
write_text(conn, message);
end_mail(conn);
END;

------------------------------------------------------------------------
FUNCTION begin_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cc IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
conn := begin_session;
begin_mail_in_session(conn,
sender,
recipients,
cc,
subject,
mime_type,
priority);
RETURN 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_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 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));
-- write_text(conn, 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');
-- Split the Base64-encoded attachment into multiple lines
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 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 end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
end_mail_in_session(conn);
end_session(conn);
END;

------------------------------------------------------------------------
FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
RETURN conn;
END;

------------------------------------------------------------------------
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
cc 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;
BEGIN
-- Specify sender's address (our server allows bogus address
-- as long as it is a full email address ([email protected]).
utl_smtp.mail(conn, get_address(my_sender));
--Specify recipient(s) of the email.
WHILE (my_recipients IS NOT NULL) LOOP
utl_smtp.rcpt(conn, get_address(my_recipients));
END LOOP;
-- Start body of email
utl_smtp.open_data(conn);
-- Set "From" MIME header
write_mime_header(conn, 'From', sender);
-- Set "To" MIME header
write_mime_header(conn, 'To', recipients);
---------------------------------------------------------------------------------------------
-- Esta parte se agrega para que tome el parámetro Cc "Con copia"
---------------------------------------------------------------------------------------------
-- Set "Con Copia a" MIME header
write_mime_header(conn, 'Cc', cc);
---------------------------------------------------------------------------------------------
-- Esta parte se agrega para que tome el parámetro Cc "Con copia"
---------------------------------------------------------------------------------------------
-- Set "Subject" MIME header
write_mime_header(conn, 'Subject', subject);
-- Set "Content-Type" MIME header
write_mime_header(conn, 'Content-Type', mime_type);
-- Set "X-Mailer" MIME header
write_mime_header(conn, 'X-Mailer', mailer_id);
-- Set priority:
-- High Normal Low
-- 1 2 3 4 5
IF (priority IS NOT NULL) THEN
write_mime_header(conn, 'X-Priority', priority);
END IF;
-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
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 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;

END;
/

Buenas, hay manera de descargar el package? porque busco y no lo encuentro.

Estos paquetes no lo puedes ver normalmente peor si no estan instalados la manera de instalar de la detallo a continuacion y mira si en la ruta indicada estan los mismos por defecto estos paquetes no se instalan al momento de crear la base los tiene que crear manualmente,.

SQL> conn / as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

Package created.


Synonym created.

SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

Package body created.

No errors.