Muhammad Nadeem Chaudhry's Blog


Sending report as email attachment in Oracle APEX

Posted in Uncategorized by Muhammad Nadeem Chaudhry on June 28, 2011

The following example shows how to use the GET_PRINT_DOCUMENT using Signature 4 (Document returns as a BLOB using XML based report data and RTF or XSL-FO based report layout). In this example, GET_PRINT_DOCUMENT is used in conjunction with APEX_MAIL.SEND and APEX_MAIL.ADD_ATTACHMENT to send an email with an attachment of the file returned by GET_PRINT_DOCUMENT. Both the report data and layout are taken from values stored in page items (P1_XML and P1_XSL).

DECLARE
    l_id number;
    l_document BLOB;
BEGIN
    l_document := APEX_UTIL.GET_PRINT_DOCUMENT (
        p_report_data         => :P1_XML,
        p_report_layout       => :P1_XSL,
        p_report_layout_type  => ‘xsl-fo’,
        p_document_format     => ‘pdf’);
 
   l_id := APEX_MAIL.SEND(
       p_to        => :P35_MAIL_TO,
       p_from      => ‘noreplies@oracle.com’,
       p_subj      => ‘sending PDF via print API’,
       p_body      => ‘Please review the attachment.’,
       p_body_html => ‘Please review the attachment’);
 
   APEX_MAIL.ADD_ATTACHMENT (
       p_mail_id    => l_id,
       p_attachment => l_document,
       p_filename   => ‘mydocument.pdf’,
       p_mime_type  => ‘application/pdf’);
END;

Example:

DECLARE
    l_id number;
    l_document BLOB;
BEGIN
    l_document := APEX_UTIL.GET_PRINT_DOCUMENT
    (
    p_application_id=>’121′,
    p_report_query_name=>’requisition’,
    p_report_layout_type=>’pdf’,
    p_document_format=>’pdf’
    );

l_id := APEX_MAIL.SEND
      (
       p_to        => ‘nadeem.zafar1@wateen.com’,      
       p_from      => ‘nadeem.zafar1@wateen.com’,
       p_subj      => ‘sending PDF via print API’,
       p_body      => ‘Please review the attachment.’,
       p_body_html => ‘Please review the attachment.’
       );

APEX_MAIL.ADD_ATTACHMENT
       (
       p_mail_id    => l_id,
       p_attachment => l_document,
       p_filename   => ‘mydocument.pdf’,
       p_mime_type  => ‘application/pdf’
       );

wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => ‘Exchange Server Name’,
P_SMTP_PORTNO => ‘Port’);

END;