Sending report as email attachment in Oracle APEX

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;