1

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

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

OR

  1. 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

  1. 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.

0

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('name@domain.net'),    
    FromMail => 'oracle@domain.net', 
    FromName => 'DB User: '||USER,
    FileName => 'abc.pdf',
    BinAttachment => pdf,
    MimeType => 'application/pdf' );

END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.