how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32

3782197 · Feb 19, 2019 11:33PM

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;

Billy Verreynne · Feb 20, 2019 12:16AM

Attach binary? That codes reads the contents of a file into a CLOB (text) - not a BLOB (binary).

A binary mail attachment needs to be Base64 encoded. See for an example of how the e-mail needs to look like, when sending it to the server via UTL_SMTP.

3782197 · Mar 12, 2019 3:28AM

The below code is attaching PDF but with 0KB (when I open pdf it has error) please advice

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_blob BLOB;

v_blob BLOB;

destOffset INTEGER:=1;

srcOffset INTEGER := 1;

lang_context INTEGER := DBMS_LOB.default_lang_ctx;

v_mail_conn utl_smtp.connection; --T37250

v_buffer_size integer := 1200; --T37250

v_smtp_server_port NUMBER := 25; --T37250

v_raw raw(57); --T37250

v_length NUMBER;

warning INTEGER;

l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';

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, '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_TCP.crlf);

IF message IS NOT NULL THEN

UTL_SMTP.write_data(v_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

UTL_SMTP.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(v_mail_conn, message);

UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

END IF;

IF att_filename IS NOT NULL THEN

UTL_SMTP.write_data(v_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

UTL_SMTP.write_data(v_mail_conn, 'Content-Type: ' || att_file_loc || '; name="' || 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, 'Content-Disposition: attachment; filename="' || att_filename || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

-- Write attachment contents

--Get the file to attach to the e-mail

v_length := dbms_lob.getlength(v_blob);

<<while_loop>>

while destOffset < v_length loop

dbms_lob.read( v_blob, v_buffer_size, destOffset, v_raw );

utl_smtp.write_raw_data( v_mail_conn, utl_encode.base64_encode(v_raw) );

utl_smtp.write_data( v_mail_conn, utl_tcp.crlf );

destOffset := destOffset + v_buffer_size;

end loop while_loop;

UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

END IF;

UTL_SMTP.write_data(v_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);

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;

Marwim · Mar 12, 2019 3:56AM

Don't try to reinvent the wheel. there are a lot of working examples out there.

Like https://oracle-base.com/articles/misc/email-from-oracle-plsql

Or to advertise mine: Oracle -> Send Mails from PL/SQL

Marcus

3782197 · Mar 13, 2019 3:31AM

I did manage to get the perfect output Thanks

PROCEDURE mail_attach_binary (

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

Forum Stats

Quick Links

Categories

how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32

Answers