Emailing pdf files using utl_smtp questions

From: hah <shhoff8_at_yahoo.com>
Date: 10 Jul 2006 11:47:55 -0700
Message-ID: <1152557275.082155.138370_at_35g2000cwc.googlegroups.com>

[Quoted] [Quoted] 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_at_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;
/ Received on Mon Jul 10 2006 - 20:47:55 CEST