Emailing pdf files using utl_smtp questions

Sybrand Bakker · *nix forums Guru Joined: 03 Apr 2005 Posts: 1766

On 11 Jul 2006 06:58:27 -0700, "hah" <shhoff8@yahoo.com> wrote:

hah · *nix forums beginner Joined: 01 Mar 2006 Posts: 4

Thank you very much, I will give that a try!

Frank van Bortel wrote:

hah · *nix forums beginner Joined: 01 Mar 2006 Posts: 4

Unfortunately database is 9, I saw the UTL_MAIL seemed much easier to
work with!

DA Morgan wrote:

psoug · *nix forums Guru Joined: 15 May 2005 Posts: 3492

hah wrote:

frank.van.bortel@gmail.co · *nix forums Guru Joined: 11 Sep 2005 Posts: 832

asktom.oracle.com
search for "email" and for "dirlist"
The first will give you the solution for attachments,
the second provides a java/pl/sql solution of reading
directories (thus: your exact file names)

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

hah · *nix forums beginner Joined: 01 Mar 2006 Posts: 4

I am using web version. I run a bunch of reports to files on the
server which the web version appends numbers to the end of the file
name. I'm having 2 problems:

First how do I get the code to recognize wildcards. I won't know the
numbers that the server appends to the filenames and I need to identify
the filename that corresponds to our employee id to send them the
correct report. Can wildcards be used in bfilename?

Also when I specify the exact filename the loop works and sends out the
pdf attachment email but only the first one is ok, all the other pdf's
are corrupt and adobe says it is not correctly decoded. ANY help would
be greatly appreciated! I am new to the web version as well as using
utl_smtp and bfile.

DECLARE
fil BFILE;
file_len PLS_INTEGER;
MAX_LINE_WIDTH PLS_INTEGER := 54;
buf RAW(2100);
amt BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */
pos PLS_INTEGER := 1; /* pointer for each piece */
filepos PLS_INTEGER := 1; /* pointer for the file */
filenm VARCHAR2(50);
v_file_name VARCHAR2(100) := 'aa.csv'; /* ascii file attachment */
v_file_handle UTL_FILE.FILE_TYPE;
v_directory_name VARCHAR2(100) := 'BFILE_DIR';
v_line VARCHAR2(1000);
conn UTL_SMTP.CONNECTION;
mesg VARCHAR2(32767);
mesg_len NUMBER;
crlf VARCHAR2(2) := chr(13) || chr(10);
data RAW(2100);
chunks PLS_INTEGER;
len PLS_INTEGER := 1;
modulo PLS_INTEGER;
pieces PLS_INTEGER;
err_num NUMBER;
err_msg VARCHAR2(100);
v_mime_type_bin varchar2(30) := 'application/pdf';

CURSOR emp_cursor is
SELECT emp_num,email from test_email;

enum varchar2(5);
email varchar2(50);
wild varchar2(2) := '%';
tdate date;

BEGIN
open emp_cursor;
LOOP
FETCH emp_cursor into enum,email;
EXIT WHEN (emp_cursor%NOTFOUND);

tdate := '01-JAN-2006';
filenm := enum||tdate||wild||'.pdf';

BEGIN

conn := demo_mail.begin_mail(sender => 'test@test.com',
recipients => email,subject => 'Missing Timesheet Report',
mime_type => demo_mail.MULTIPART_MIME_TYPE);
END begin_mail;
BEGIN
demo_mail.attach_text(conn => conn,data => '<h1>Attachment is
Working</h1>' || crlf || crlf,mime_type => 'text/html');
END attach_text;
BEGIN
demo_mail.begin_attachment(conn => conn,mime_type =>
'text/plain',inline => TRUE,filename => v_file_name,
transfer_enc => '7 bit');
begin
v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r');
loop
utl_file.get_line(v_file_handle, v_line);
mesg := v_line || crlf;
demo_mail.write_text(
conn => conn,
message => mesg);
end loop;
exception
when others then
null;
end;
utl_file.fclose(v_file_handle);
demo_mail.end_attachment(
conn => conn );
END begin_attachment;
BEGIN
demo_mail.begin_attachment(conn => conn,mime_type =>
v_mime_type_bin,inline => TRUE,filename => filenm, transfer_enc =>
'base64');
BEGIN
fil := BFILENAME('BFILE_DIR', filenm);
dbms_output.put_line(filenm);
file_len := dbms_lob.getlength(fil);
modulo := mod(file_len, amt);
pieces := trunc(file_len / amt);
if (modulo <> 0) then
pieces := pieces + 1;
end if;
dbms_lob.fileopen(fil, dbms_lob.file_readonly);
dbms_lob.read(fil, amt, filepos, buf);
data := NULL;
FOR i IN 1..pieces LOOP
filepos := i * amt + 1;
file_len := file_len - amt;
data := utl_raw.concat(data, buf);
chunks := trunc(utl_raw.length(data) / MAX_LINE_WIDTH);
IF (i <> pieces) THEN
chunks := chunks - 1;
END IF;
demo_mail.write_raw( conn => conn,
message => utl_encode.base64_encode(data ) );
data := NULL;
if (file_len < amt and file_len > 0) then
amt := file_len;
end if;
dbms_lob.read(fil, amt, filepos, buf);
END LOOP;
END;

dbms_lob.fileclose(fil);
demo_mail.end_attachment(conn => conn );
END begin_attachment;
demo_mail.end_mail(conn => conn);

END LOOP;
close emp_cursor;

EXCEPTION
when no_data_found then
demo_mail.end_attachment( conn => conn );
dbms_lob.fileclose(fil);
when others then
demo_mail.end_attachment( conn => conn );
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('Error number is ' || err_num);
dbms_output.put_line('Error message is ' || err_msg);
dbms_lob.fileclose(fil);
END sendit;
/

Google