Home » SQL & PL/SQL » SQL & PL/SQL » how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32 (Oracle Database 12c)
how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32 [message #674863] Tue, 19 February 2019 21:55 Go to next message
Nembezah
Messages: 11
Registered: September 2017
Junior Member
The base procedure mail_attach_binary(created below) to e-mail binary files
from a directory location on the database (/home/alert)


My Question is from here how can at
how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32000 bytes?


--SPEC
PROCEDURE mail_attach_binary
(recipients VARCHAR2,
cc VARCHAR2 DEFAULT NULL,
subject VARCHAR2,
message VARCHAR2 DEFAULT NULL,
att_filename VARCHAR2 DEFAULT NULL,
att_file_loc VARCHAR2);
END SPP_EMAIL;

--BODY
PROCEDURE mail_attach_binary
(recipients VARCHAR2,
cc VARCHAR2,
subject VARCHAR2,
message VARCHAR2,
att_filename VARCHAR2,
att_file_loc VARCHAR2) AS
--file attachment paramaters
v_bfile BFILE;
v_clob CLOB;
destOffset INTEGER:=1;
srcOffset INTEGER := 1;
lang_context INTEGER := DBMS_LOB.default_lang_ctx;
warning INTEGER;

-- v_mime_type VARCHAR2(30) := 'application/pdf';

BEGIN

setup_smtp_server;
--Get the file to attach to the e-mail
v_bfile := BFILENAME (att_file_loc, att_filename);
DBMS_LOB.OPEN (v_bfile);
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);
DBMS_LOB.LOADCLOBFROMFILE(
dest_lob => v_clob,
src_bfile => v_bfile,
amount => DBMS_LOB.GETLENGTH(v_bfile),
dest_offset => destOffset,
src_offset => srcOffset,
bfile_csid => DBMS_LOB.default_csid,
lang_context => lang_context,
warning => warning);
DBMS_LOB.CLOSE(v_bfile);

EXCEPTION WHEN
INVALID_ARGUMENT THEN
alert('EMAIL',1000,'Invalid argument passed to e-mail attachment from utl_mail.send_attach_varchar2');
END mail_attach_binary;
Re: how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32 [message #675136 is a reply to message #674863] Tue, 12 March 2019 07:02 Go to previous messageGo to next message
Nembezah
Messages: 11
Registered: September 2017
Junior Member
email send pdf with email body

PROCEDURE mail_attach_binary (
recipients VARCHAR2,
cc VARCHAR2,
subject VARCHAR2,
message VARCHAR2,
att_filename VARCHAR2,
att_file_loc VARCHAR2
) AS
--file attachment paramaters

v_bfile BFILE;
--v_clob CLOB; --T37250
destoffset INTEGER := 1;
--srcoffset INTEGER := 1; --T37250
--lang_context INTEGER := dbms_lob.default_lang_ctx;--T37250
warning INTEGER;
v_mail_conn utl_smtp.connection; --T37250
v_smtp_server_port NUMBER := 25; --T37250
v_length INTEGER :=0; --T37250
v_raw RAW(57); --T37250
v_buffer_size INTEGER := 57; --T37250
l_boundary CONSTANT VARCHAR2(256) := '7D81B75CCC90D2974F7A1CBD'; --T37250
first_boundary CONSTANT VARCHAR2(256) := '--'|| l_boundary|| utl_tcp.crlf; --T37250
last_boundary CONSTANT VARCHAR2(256) := '--'|| l_boundary|| '--'|| utl_tcp.crlf; --T37250
multipart_mime_type CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'|| l_boundary|| '"'; --T37250

BEGIN
setup_smtp_server;
v_mail_conn := utl_smtp.open_connection(v_smtp_server, v_smtp_server_port);
utl_smtp.helo(v_mail_conn, v_smtp_server_port);
utl_smtp.mail(v_mail_conn, v_sender_email);
utl_smtp.rcpt(v_mail_conn, recipients);

utl_smtp.open_data(v_mail_conn);

utl_smtp.write_data(v_mail_conn, 'Date: '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')|| utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'To: '|| recipients|| utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'From: '|| v_sender_email|| utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'Subject: '|| subject|| utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'This is email body' ||utl_tcp.CRLF);

--Use MIME mail standard
utl_smtp.write_data(v_mail_conn, 'MIME-Version: 1.0' || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="'|| l_boundary|| '"'|| utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);

-- Write the plain text portion of the email in Message body
IF message IS NOT NULL THEN
utl_smtp.write_data(v_mail_conn, first_boundary);
utl_smtp.write_data(v_mail_conn, 'Content-Type: text/plain;'|| utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, ' charset=US-ASCII' || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, message ||utl_tcp.crlf);
END IF;

-- Content of attachment
utl_smtp.write_data(v_mail_conn, first_boundary);
utl_smtp.write_data(v_mail_conn, 'Content-Type'||':'||'application/pdf'|| utl_tcp.crlf);
utl_smtp.write_data (v_mail_conn, 'Content-Disposition: attachment; ' || utl_tcp.crlf);
utl_smtp.write_data (v_mail_conn, ' filename="' || att_filename || '"' || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);


--Get the file to attach to the e-mail
v_bfile := bfilename(att_file_loc, att_filename);
dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);

-- Send the email byte chunks to UTL_SMTP

-- Get the size of the file to be attached
v_length := dbms_lob.getlength(v_bfile);
<< while_loop >>
WHILE destoffset < v_length LOOP
dbms_lob.read(v_bfile, v_buffer_size, destoffset, v_raw);
utl_smtp.write_raw_data(v_mail_conn, utl_encode.base64_encode(v_raw));
destoffset := destoffset + v_buffer_size;
END LOOP;

utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn,last_boundary);
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);

--close SMTP connection and LOB file
DBMS_LOB.filecloseall;
dbms_lob.fileclose(v_bfile);
utl_smtp.close_data(v_mail_conn);
utl_smtp.quit(v_mail_conn);
EXCEPTION
WHEN invalid_argument THEN
jfpm_spp_alert('EMAIL', 1000, 'Invalid argument passed to e-mail attachment from utl_mail.send_attach_varchar2');
END mail_attach_binary;
Re: how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32 [message #675139 is a reply to message #675136] Tue, 12 March 2019 07:40 Go to previous message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

https://community.oracle.com/thread/4202231
Previous Topic: Replacing bulk collect with loop
Next Topic: SQL Data Extraction for CLOB
Goto Forum:
  


Current Time: Sat Aug 08 09:06:57 CDT 2020