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;

Advertisements

3 Responses to 'Sending report as email attachment in Oracle APEX'

Subscribe to comments with RSS or TrackBack to 'Sending report as email attachment in Oracle APEX'.

  1. Osman said,

    How can I stored report data and layout are taken from values stored in page items (P1_XML and P1_XSL).
    Thanks


    • Usman,
      – Create report using report query.
      – Create desired report layout.
      – Reference that saved report as

      p_application_id=>’Get the application ID′
      p_report_query_name=>’Name of the report query’
      p_report_layout_type=>’Layout type of the report query’
      p_document_format=>’Document Format’.

      Read the given example.

  2. Vance said,

    Hi, you post interesting content on your page, you deserve much more visitors, just type in google for – augo’s tube
    traffic


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: