Emailing pdf files using utl_smtp questions
hah
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 => 'te...@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;
/
Frank van Bortel
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...
DA Morgan
> 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.
What version of Oracle?
If 10g you should be using UTL_MAIL not UTL_SMTP.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
hah
work with!
hah
Sybrand Bakker
>Unfortunately database is 9, I saw the UTL_MAIL seemed much easier to
>work with!
Please stop top posting.
--
Sybrand Bakker, Senior Oracle DBA