This discussion is archived
6 Replies Latest reply: Sep 20, 2013 6:30 AM by Mike Kutz RSS

How to automatically send report after it is created?

874887 Newbie
Currently Being Moderated

Hello,

 

I am beginner and I would appreciate if someone could tell me general information.

 

I found this tutorial how to save and access report.

Storing and Accessing Reports in the Database

 

In that tutorial report is saved onclick.

Is there a way to automatically email report everytime report is generate or I should first save it (PDF, CSV,..) with SQL script and then email it?

 

I also found that in Interactive report there is page process that can send e-mails. Is it possible that page process send e-mail with report or report should be also saved before sending?

 

The reason for this is that we want to have a history of created reports.

 

Thank you in advance.

  • 1. Re: How to automatically send report after it is created?
    Mike Kutz Expert
    Currently Being Moderated

    874887 wrote:


    The reason for this is that we want to have a history of created reports.

     

    Save report, then email it.

    I usually call COMMIT between the two steps.

     

    Use the APEX_MAIL package to send out an e-mail with an attachment.

     

    You should be able to modify the PL/SQL to do it all in one step

     

    declare
      l_report blob;
    -- Added this for APEX_MAIL
    l_mail_id NUMBER;
    begin
      l_report := apex_util.get_print_document (
      p_application_id => :APP_ID,
      p_report_query_name => 'blobquery',
      p_report_layout_name => 'blobquery',
      p_report_layout_type => 'rtf',
      p_document_format => 'pdf'
      );
      insert into report_archive (
      filename,
      mimetype,
      report,
      created_date,
      created_by
      ) values (
      'BLOB Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')',
      'application/pdf',
      l_report,
      sysdate,
      :USER
      );
    -- COMMIT the insert so you don't e-mail out a report
    -- that was never saved
    commit;
    -- create e-mail here
    l_mail_id := APEX_MAIL.send( ... ); -- see document for usage
    APEX_MAIL.ADD_ATTACHMENT( .. ); -- see document for usage
    end;



    MK


  • 2. Re: How to automatically send report after it is created?
    874887 Newbie
    Currently Being Moderated

    Hi Mike,

    thank you for a quick replay and help.

    I have additional question. I can't figure out where should I place this code.

    At the moment I placed it in report under "Page Processing" -> "After Processing".

    Report is saved in report_archive but report field is empty.

    Thank you.

  • 3. Re: How to automatically send report after it is created?
    Mike Kutz Expert
    Currently Being Moderated

    This is how I usually set something like this up:

    • create an 'email page' button that does a 'submit' but skips validation.
    • create a process -> pl/sql anonymous block -> "on submit - after computation and validation"
    • ..and select the "email page" button for the "process Condition"
    • Maybe, create a branch point for that button.

     

    874887 wrote:

    Report is saved in report_archive but report field is empty.

    Thank you.

    How are you determining that the 'report field' is empty?

    Take a look at the SQL in Step 6 from your link.

    Modify that so it produces report_length in two columns.

    eg

    SELECT id,
      filename,
      mimetype,
      created_date,
      created_by,
      dbms_lob.getlength(report) report_length,
      dbms_lob.getlength(report) report_length2
    FROM report_archive

     

    Now, when you see the report, you should see how big the file is.

    If the 'report_length2' column is empty or 0, then - the report is not being saved.

     

    PS - you have to provide the FULL file name... including the '.pdf' extension.

  • 4. Re: How to automatically send report after it is created?
    874887 Newbie
    Currently Being Moderated

    Hello Mike,

     

    Thank you for help. I spend all day trying to figure out what am I doing wrong. I googled a lot.

    I am noob so there might be some completely stupid mistake.

    I am using code you posted. I just changed value for PDF name.

    I get BLOB written in database but it is empty, filename extension is PDF. I see size of field by doubleclicking field it and I also used qry to check size.

    I would appreciate if you could take a look.

     

    declare 
      l_report blob; 
    -- Added this for APEX_MAIL 
    l_mail_id NUMBER; 
    begin 
      l_report := apex_util.get_print_document ( 
      p_application_id => :APP_ID, 
      p_report_query_name => 'blobquery', 
      p_report_layout_name => 'blobquery', 
      p_report_layout_type => 'rtf', 
      p_document_format => 'pdf' 
      ); 
      insert into report_archive ( 
      filename, 
      mimetype, 
      report, 
      created_date, 
      created_by 
      ) values ( 
      'BLOB_report_'||to_char(sysdate,'DDMonYYYY')||'.pdf', 
      'application/pdf', 
      l_report, 
      sysdate, 
      :USER 
      ); 
    -- COMMIT the insert so you don't e-mail out a report 
    -- that was never saved 
    commit;
  • 5. Re: How to automatically send report after it is created?
    AndyH Journeyer
    Currently Being Moderated

    Presumably you have configured FOP or BI Publisher as your print server through your APEX instance 'Report Printing'?

  • 6. Re: How to automatically send report after it is created?
    Mike Kutz Expert
    Currently Being Moderated

    The code looks correct.  Just, make sure you are not 'hiding' the error with a WHEN OTHERS in you EXCEPTION clause (if you have one.)

    What is the 'size' that is reported for the file?

    In the App Builder, for your Application, in the Shared Components section, on the bottom right (Reports section):

    do you have a Report Query by the name of 'blobquery'?

    do you have a Report Layout by the name of 'blobquery'?

     

    Between lines 12 and 13, add the following code to check that a PDF was made.

    note : I'm not sure if the 2nd section will work.  you may have to comment it out.

     

    -- check that blob was created and that it is PDF
    if l_blob is null or dbms_lob.getLength( l_blob ) = 0
    then
      raise_application_error( -20001, 'PDF size=0.  Report was not generated');
    elsif '%PDF' != utl_raw.cast_to_varchar2( dbms_lob.substr( l_blob, 4, 1 ) )
    then
      raise_application_error( -20002, 'File created is not a PDF' );
    end if;

     

    If it produces an error... that means the get_print_report() is not producing a PDF file.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points