one for the experts


I have been working on a procedure to send pdf email attachments and have come across a problem that is testing my patience.

I am trying to send two types of pdf, one created in oracle reports and one created in microsoft access.

The pdf i send from oracle reports opens ok and the pdf can be viewed, however the pdf from access opens ok, regonises the number of pages but it is just blank pages.

I am using oracle 9i db and the files are stored on the server in the same place. We transfer the pdf from ms access over by ftp in binary mode.

I have attached my code below

create or replace
PROCEDURE send_email_attach_SS( sender IN VARCHAR2,
recipient IN VARCHAR2,
SUBJECT IN VARCHAR2,
MESSAGE IN VARCHAR2,
FILENAME1 IN VARCHAR2 DEFAULT NULL,
FILENAME2 IN VARCHAR2 DEFAULT NULL,
FILENAME3 IN VARCHAR2 DEFAULT NULL,
Cc IN VARCHAR2 DEFAULT NULL) IS

L_SMTP_SERVER VARCHAR2(20); /** TO STORE THE IP ADDRESS OF THE SMTP SERVER **/
L_FILE_NAME VARCHAR2(100); /** TO STORE THE FILENAME **/
CRLF VARCHAR2(2):= CHR(13) || CHR(10);
L_MESG VARCHAR2(32767); /** TO STORE THE MESSAGE **/
CONN UTL_SMTP.CONNECTION; /** SMTP CONNECTION VARIABLE **/
TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
FILE_ARRAY VARCHAR2_TABLE; /** AN ARRAY TO STORE THE FILE NAMES **/
I BINARY_INTEGER; /** ARRAY INDEX **/
L_SLASH_POS NUMBER; /** TO STORE THE POSITION OF IN THE FILE NAME **/
ABORT_PROGRAM EXCEPTION; /** USER DEFINED EXCEPTION **/
RETURN_DESC1 VARCHAR2(2000); /** VARIABLE TO STORE THE ERROR MESSAGE. TO BE RETURNED TO THE CALLING PROGRAM **/
RETURN_ERROR VARCHAR2(50); /** VARIABLE TO STORE THE ERROR PROCEDURE. TO BE RETURNED TO THE CALLING PROGRAM **/
VLOOP NUMBER := 0;

V_BFILE BFILE ;
V_LGH_FILE BINARY_INTEGER;
VBUFFER RAW (32767);
L_AMOUNT BINARY_INTEGER := 1024;
L_POS PLS_INTEGER := 1;




/**** MAIN PROGRAM STARTS HERE ****/

BEGIN

Begin
Select sch_email_ip_addr
Into L_SMTP_SERVER
From com_schemes;
End;

/*** ASSIGNING FILE NAMES TO ARRAY ***/
FILE_ARRAY(1) := FILENAME1;
FILE_ARRAY(2) := FILENAME2;
FILE_ARRAY(3) := FILENAME3;
RETURN_DESC1 := 'THERE WAS AN ERROR IN OPENING CONNECTION.';
CONN:= UTL_SMTP.OPEN_CONNECTION( L_SMTP_SERVER);
UTL_SMTP.HELO( CONN, L_SMTP_SERVER );
UTL_SMTP.MAIL( CONN, sender );
UTL_SMTP.RCPT( CONN, recipient );
IF (Cc IS NOT NULL) THEN
UTL_SMTP.RCPT( CONN, cc );
END IF;
UTL_SMTP.OPEN_DATA ( CONN );

/*** GENERATE THE MIME HEADER ***/
RETURN_DESC1 := 'THERE WAS AN ERROR IN GENERATING MIME HEADER.';
IF (CC IS NOT NULL) THEN
L_MESG:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || CRLF ||
'From: ' || sender || CRLF || 'Subject: ' || SUBJECT || CRLF ||
'To: ' || recipient || CRLF || 'Cc: ' || cc || CRLF ||
'Mime-Version: 1.0' || CRLF ||
'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || CRLF ||
'' || CRLF ||
'This is a Mime message, which your current mail reader may not' || CRLF ||
'understand. Parts of the message will appear as text. If the remainder' || CRLF ||
'appears as random characters in the message body, instead of as' || CRLF ||
'attachments, then you''ll have to extract these parts and decode them' || CRLF ||
'manually.' || CRLF || '' || CRLF ||
'--DMW.Boundary.605592468' || CRLF ||
'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || CRLF ||
'Content-Disposition: inline; filename="message.txt"' || CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF ||
'' || CRLF || MESSAGE || CRLF || CRLF || CRLF ;
ELSE
L_MESG:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || CRLF ||
'From: ' || sender || CRLF || 'Subject: ' || SUBJECT || CRLF ||
'To: ' || recipient || CRLF || 'Mime-Version: 1.0' || CRLF ||
'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || CRLF ||
'' || CRLF ||
'This is a Mime message, which your current mail reader may not' || CRLF ||
'understand. Parts of the message will appear as text. If the remainder' || CRLF ||
'appears as random characters in the message body, instead of as' || CRLF ||
'attachments, then you''ll have to extract these parts and decode them' || CRLF ||
'manually.' || CRLF || '' || CRLF ||
'--DMW.Boundary.605592468' || CRLF ||
'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || CRLF ||
'Content-Disposition: inline; filename="message.txt"' || CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF ||
'' || CRLF || MESSAGE || CRLF || CRLF || CRLF ;
END IF;


RETURN_DESC1 := 'THERE WAS AN ERROR IN WRITING MESSAGE TO CONNECTION.';
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );

/*** START ATTACHING THE FILES ***/
FOR I IN 1..3 LOOP

IF FILE_ARRAY(I) IS NOT NULL THEN

BEGIN
-- L_FILE_NAME := FILE_ARRAY(I);
RETURN_DESC1 := 'THERE WAS AN ERROR IN OPENING FILE.';
V_BFILE := BFILENAME('EMAIL_ATTACH', FILE_ARRAY(I));
dbms_lob.fileopen(V_BFILE, dbms_lob.file_readonly);
V_LGH_FILE := dbms_lob.getlength(V_BFILE);

L_MESG := CRLF || '--DMW.Boundary.605592468' || CRLF ||
'Content-Type: application/pdf; name="' || FILE_ARRAY(I) || '"' || CRLF ||
'Content-Disposition: attachment; filename="' || FILE_ARRAY(I) || '"' || CRLF ||
'Content-Transfer-Encoding: 8bit ' || CRLF || CRLF ;
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
L_POS := 1;
WHILE L_POS < V_LGH_FILE
LOOP
VLOOP := VLOOP + 1;
RETURN_DESC1 := VLOOP || '-THERE WAS AN ERROR IN READING FILE.';
dbms_lob.read(V_BFILE, L_AMOUNT, L_POS, VBUFFER);
IF VBUFFER IS NOT NULL THEN
UTL_SMTP.WRITE_DATA ( CONN,utl_raw.cast_to_varchar2(VBUFFER));
VBUFFER := '';
END IF;
L_POS := L_POS + L_AMOUNT;
END LOOP;
END;

L_MESG := CRLF;
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
dbms_lob.fileclose(V_BFILE);

END IF;

END LOOP;

RETURN_DESC1 := '70 - E: THERE WAS AN ERROR IN CLOSING MIME BOUNDARY. ';
L_MESG := CRLF || '--DMW.Boundary.605592468--' || CRLF;
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
UTL_SMTP.CLOSE_DATA( CONN );
UTL_SMTP.QUIT( CONN );

EXCEPTION
WHEN ABORT_PROGRAM THEN
utl_smtp.quit(CONN);
raise_application_error(-20000,
'Failed to send mail due to the following error:'|| RETURN_ERROR ||'/'|| RETURN_DESC1||'/'||FILE_ARRAY(I),true);

WHEN OTHERS THEN
utl_smtp.quit(CONN);
raise_application_error(-20000,
'Failed to send mail due to the following error:' || RETURN_ERROR ||'/'|| RETURN_DESC1||'/'||FILE_ARRAY(I),true);

END;
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories