Generate & Email report from Oracle Forms 11g

Question

I generate a report in PDF through passing of parameters from Oracle Forms 11g. I have a requirement to email the same report automatically as well. Following is the part of forms where I have passed on the parameters.

pl_id := Create_Parameter_List('tmpdata1'); 
add_parameter(pl_id, 'FDT', TEXT_PARAMETER, :a);
add_parameter(pl_id, 'TDT', TEXT_PARAMETER, :b);
add_parameter(pl_id, 'VC', TEXT_PARAMETER, :e);
add_parameter(pl_id, 'SYS_DATE', TEXT_PARAMETER, :f);
ADD_PARAMETER(pl_id,'DESTYPE',TEXT_PARAMETER,'FILE');
ADD_PARAMETER(pl_id,'DESFORMAT',TEXT_PARAMETER,'PDF');
ADD_PARAMETER(pl_id,'DESNAME',TEXT_PARAMETER,'C:\Downloads\abc.pdf'); 

Now a copy of report is saved in C:\Downloads folder as well as its shown in browser.

I use the following procedure to send current data shown in Forms via emails.

create or replace procedure send4 (p_sender IN VARCHAR2, p_recipient IN VARCHAR2, p_subject IN VARCHAR2, p_message IN VARCHAR2)
IS
crlf VARCHAR2(2) := chr(13)||chr(10);
l_mailhost VARCHAR2(255) := <IP ADDRESS>;
v_connection UTL_SMTP.connection;
BEGIN
V_CONNECTION := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.Helo(V_CONNECTION, l_mailhost);
utl_smtp.Mail(V_CONNECTION, p_sender);
utl_smtp.Rcpt(V_CONNECTION, p_recipient);
utl_smtp.Data(V_CONNECTION,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || p_sender || crlf ||
'Subject: '|| p_subject || crlf ||
'To: ' || p_recipient || crlf ||
'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
p_message|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="file.txt"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="attachment.txt"'|| crlf ||
crlf ||
p_message|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--' -- End MIME mail
);
UTL_SMTP.quit(v_connection);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail', TRUE);
END;

1. Can I email the Report generated directly? I tried passing the parameter 'MAIL' and 'Email address' in destype and desname but I am guessing there is some settings need to be done?

OR

2. How can I use the email procedure to send the said report as attachment?

OR

3. How can I copy the report pdf generated in user's PC to a specific directory at server? Because I have one separate procedure for email where I can mail PDFs from server directly. User has Windows PC while server is LINUX.

OR

4. Generate report in PDF format at server directly so I could use the procedure (#3 point) to email it?

I am posting this here in Database Administrators because I believe there needs to be a database level configuration done (developers have not been able to help). Sorry if its a wrong forum to post.

Answer 1

You can download a file from filesystem like this:

FUNCTION GetFile(FolderName IN VARCHAR2, FileName IN VARCHAR2) RETURN BLOB IS
 xbfile BFILE; 
 xblob BLOB; 
 destOffset INTEGER := 1; 
 srcOffset INTEGER := 1; 
BEGIN 
 xbfile := BFILENAME (FolderName, FileName); 
 DBMS_LOB.OPEN (xbfile); 
 DBMS_LOB.CREATETEMPORARY(xblob, TRUE, DBMS_LOB.SESSION); 
 DBMS_LOB.LOADBLOBFROMFILE(
 dest_lob => xblob, 
 src_bfile => xbfile, 
 amount => DBMS_LOB.GETLENGTH(xbfile), 
 dest_offset => destOffset, 
 src_offset => srcOffset); 
 DBMS_LOB.CLOSE(xbfile); 
 RETURN xblob; 
END GetFile;

In order to access filesystem you must create a DIRECTORY (if not already exists):

CREATE OR REPLACE DIRECTORY PDF_REPORTS AS 'C:\Downloads';

In my application I use this generic package to send mails:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
/
CREATE OR REPLACE PACKAGE Mailing AS
PRIORITY_HIGH CONSTANT INTEGER := 1;
PRIORITY_NORMAL CONSTANT INTEGER := 3;
PRIORITY_LOW CONSTANT INTEGER := 5;
MIME_MIXED CONSTANT VARCHAR2(50) := 'multipart/mixed;';
MIME_TEXT CONSTANT VARCHAR2(50) := 'text/plain;';
MIME_HTML CONSTANT VARCHAR2(50) := 'text/html;'; 
PROCEDURE SendMail(
 Subject IN VARCHAR2, 
 Message IN OUT CLOB, 
 ToMail IN VARCHAR_TABLE_TYPE, 
 FromMail IN VARCHAR2, FromName IN VARCHAR2,
 PRIORITY IN INTEGER DEFAULT PRIORITY_NORMAL,
 FileName IN VARCHAR2 DEFAULT NULL, 
 MimeType IN VARCHAR2 DEFAULT MIME_TEXT, --> determines the MIME-Type of binary attachment "BinAttachment"
 TxtAttachment IN CLOB DEFAULT NULL, 
 BinAttachment IN BLOB DEFAULT NULL);
END Mailing;
/
CREATE OR REPLACE PACKAGE BODY Mailing AS
PROCEDURE SendMail(
 Subject IN VARCHAR2, 
 Message IN OUT CLOB, 
 ToMail IN VARCHAR_TABLE_TYPE, 
 FromMail IN VARCHAR2, FromName IN VARCHAR2,
 PRIORITY IN T_MAIL_PRIORITY DEFAULT PRIORITY_NORMAL,
 FileName IN VARCHAR2 DEFAULT NULL, 
 MimeType IN VARCHAR2 DEFAULT MIME_TEXT,
 TxtAttachment IN CLOB DEFAULT NULL, 
 BinAttachment IN BLOB DEFAULT NULL) IS
 MIME_BOUNDARY CONSTANT VARCHAR2(50) := '====Multipart.Boundary.689464861147414354====';
 con UTL_SMTP.CONNECTION;
 ret UTL_SMTP.REPLY;
 Charset VARCHAR2(20);
 Footer VARCHAR2(1000);
 Recipients VARCHAR2(1000);
 LobLen PLS_INTEGER;
 amount PLS_INTEGER := 8192;
 BUFFER VARCHAR2(16384);
 OFFSET PLS_INTEGER := 1;
 isHTML BOOLEAN := REGEXP_LIKE(DBMS_LOB.SUBSTR(Message, 1000, 1), '(< *html)|(< *body)', 'i');
BEGIN
 SELECT UTL_I18N.MAP_CHARSET(VALUE)
 INTO Charset
 FROM NLS_DATABASE_PARAMETERS
 WHERE parameter = 'NLS_CHARACTERSET';
 -- setup mail header
 con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
 ret := UTL_SMTP.HELO(con, SYS_CONTEXT('USERENV', 'DB_DOMAIN'));
 ret := UTL_SMTP.MAIL(con, FromMail);
 FOR i IN ToMail.FIRST..ToMail.LAST LOOP
 Recipients := Recipients ||ToMail(i)||',';
 ret := UTL_SMTP.RCPT(con, ToMail(i));
 END LOOP;
 ret := UTL_SMTP.OPEN_DATA(con);
 IF REGEXP_LIKE(FromName, '^[ -~]+$') THEN
 UTL_SMTP.WRITE_DATA(con, 'From: "'||FromName||'" <'||FromMail||'>'||UTL_TCP.CRLF);
 ELSE
 UTL_SMTP.WRITE_DATA(con, 'From: =?UTF-8?B?'|| UTL_ENCODE.TEXT_ENCODE(FromName, NULL, UTL_ENCODE.BASE64) ||'?= <'||FromMail||'>'||UTL_TCP.CRLF);
 END IF; 
 UTL_SMTP.WRITE_DATA(con, 'To: '||REGEXP_REPLACE(Recipients, ',$')||UTL_TCP.CRLF);
 IF REGEXP_LIKE(Subject, '^[ -~]+$') THEN
 UTL_SMTP.WRITE_DATA(con, 'Subject: '||Subject||UTL_TCP.CRLF);
 ELSE
 UTL_SMTP.WRITE_DATA(con, 'Subject: =?UTF-8?B?'|| REPLACE(REPLACE(UTL_ENCODE.TEXT_ENCODE(Subject, NULL, UTL_ENCODE.BASE64), CHR(13), NULL), CHR(10), NULL) ||'?='||UTL_TCP.CRLF);
 END IF;
 UTL_SMTP.WRITE_DATA(con, 'Date: '||TO_CHAR(CURRENT_TIMESTAMP, 'Dy, DD Mon YYYY hh24:mi:ss TZHTZM', 'NLS_DATE_LANGUAGE = American')||UTL_TCP.CRLF); 
 UTL_SMTP.WRITE_DATA(con, 'X-Priority: '||PRIORITY||UTL_TCP.CRLF);
 IF FileName IS NOT NULL THEN
 UTL_SMTP.WRITE_DATA(con, 'Mime-Version: 1.0' || UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MIME_MIXED||' boundary="'||MIME_BOUNDARY||'"' || UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, 'This is a multipart message in MIME format.' || UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
 END IF;
 -- Append common footer to mail 
 Footer := 'Message from '||SYS_CONTEXT('USERENV', 'DB_NAME')||' sent at '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
 IF isHTML THEN
 UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_HTML||' charset='||Charset || UTL_TCP.CRLF);
 Message := REPLACE(message, '</body>', '<p>'||Footer||'</p></body>');
 ELSE 
 UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_TEXT||' charset='||Charset || UTL_TCP.CRLF);
 END IF;
 -- Mail Body
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
 LobLen := DBMS_LOB.GETLENGTH(Message);
 LOOP
 EXIT WHEN OFFSET > LobLen;
 DBMS_LOB.READ(Message, amount, OFFSET, BUFFER);
 UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
 OFFSET := OFFSET + amount;
 END LOOP; 
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
 IF NOT isHTML THEN
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF || UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, Footer);
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
 END IF;
 IF FileName IS NOT NULL THEN
 -- Mail Attachment
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MimeType||' name="'||Filename||'"'|| UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: attachment; filename="'||Filename||'"'|| UTL_TCP.CRLF);
 OFFSET := 1;
 IF TxtAttachment IS NOT NULL THEN
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF); 
 LobLen := DBMS_LOB.GETLENGTH(TxtAttachment);
 LOOP
 EXIT WHEN OFFSET > LobLen;
 DBMS_LOB.READ(TxtAttachment, amount, OFFSET, BUFFER);
 UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
 OFFSET := OFFSET + amount;
 END LOOP;
 ELSIF BinAttachment IS NOT NULL THEN
 amount := 48; -- must be a whole multiple of 3
 UTL_SMTP.write_data(con, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF); 
 LobLen := DBMS_LOB.GETLENGTH(BinAttachment);
 LOOP
 EXIT WHEN OFFSET > LobLen;
 DBMS_LOB.READ(BinAttachment, amount, OFFSET, BUFFER);
 UTL_SMTP.WRITE_RAW_DATA(con, UTL_ENCODE.BASE64_ENCODE(BUFFER));
 OFFSET := OFFSET + amount;
 END LOOP; 
 END IF;
 UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
 UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY||'--' || UTL_TCP.CRLF);
 END IF;
 -- finish mail
 ret := UTL_SMTP.CLOSE_DATA(con);
 ret := UTL_SMTP.QUIT(con);
EXCEPTION
 WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
 UTL_SMTP.QUIT(con);
END SendMail;
END Mailing;
/

It provides some extras:

• You can optionally attach a file (txt or binary, but only one file is supported)
• From and Subject may contain Non-ASCII characters like öäü (of course the mail Body may also)
• Multiple recipients
• Automatically use correct character set from database
• Automatically detects plain-text or HTML mail body
• Common footer to indicate mail

Then you can call the function like

DECLARE
 pdf BLOB;
 Message CLOB := 'Some message text';
BEGIN
 pdf := GetFile('PDF_REPORTS','abc.pdf');
Mailing.SendMail(
 Subject => 'The Subject', 
 Message => Message, 
 ToMail => VARCHAR_TABLE_TYPE('[email protected]'), 
 FromMail => '[email protected]', 
 FromName => 'DB User: '||USER,
 FileName => 'abc.pdf',
 BinAttachment => pdf,
 MimeType => 'application/pdf' );
END;