BI Report Print Button Example Oracle Apex.

In this tutorial, I am giving an example to load BI Publisher report content to a BLOB field in Oracle Apex.

For example, you are calling a BI publisher report to generate Payslips in PDF format from the employee screen using a Print button and at the same point, you want that PDF report to load to a BLOB field into another table so that you can use it further. To perform such a task, follow these steps:

  1. First, create a table to store the BLOB content of a payslip PDF file. Create a table structure as follows:
CREATE TABLE EMP_PAYSLIPS 
   (EMPNO NUMBER, 
  FILENAME VARCHAR2(200), 
  REPORT BLOB, 
  MIMETYPE VARCHAR2(100), 
  CREATED_DATE DATE, 
  CREATED_BY VARCHAR2(100)
   );

ALTER TABLE EMP_PAYSLIPS ADD CONSTRAINT PK_EMPPAYSLIPS
PRIMARY KEY (EMPNO);

The above table will store the payslip PDF against the employee number (EMPNO).

  1. Now open the page in Oracle Apex page designer in which you are calling the Payslip BI publisher report. To demonstrate this, I have created a Report Layout named Payslip and a Report Query named employee_payslip_query.
  2. Then call the following PL/SQL procedure from the button you are calling the BI publisher report or create a process or dynamic action, Oracle Apex has a lot of ways to do this:
declare
  l_report blob;
begin
  l_report := apex_util.get_print_document (
    p_application_id      => :APP_ID,
    p_report_query_name   => 'employee_payslip_query',
    p_report_layout_name  => 'Payslip',
    p_report_layout_type  => 'rtf',
    p_document_format     => 'pdf'
  );
  
  -- delete if already exists
  Delete from emp_payslips where empno = V('P3_EMPNO');
  
  insert into emp_payslips (
   empno,
   filename,
   mimetype,
   report,
   created_date,
   created_by
  ) values (V('P3_EMPNO'),
   'PAYSLIP_'||V('P3_EMPNO')||'.PDF',
   'application/pdf',
   l_report,
   sysdate,
   :USER
  );
EXCEPTION
WHEN OTHERS THEN
   NULL;
/* HANDLE ERROR HERE IF ANY */
end;

Note: The Report Query, and Report Layout names are case sensitive, so when calling the APEX_UTIL.GET_PRINT_DOCUMENT function, make sure to specify correct names.

Now save the changes and run the page and then query the table you will find the data inserted through the above procedure.

See also:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

Leave a Reply

This Post Has 8 Comments

  1. Mohamed Sameer

    Hi vinish,

    I found your site as pretty useful, thanks a lot.

    On this above tutorial, everything was working fine awesome man.

    But, when attachment sent to email the file got corrupted. please provide any workaround for this issue.

    Thanks.

    1. Vinish Kapoor

      Remove the exception part of the above PL/SQL program to see if any error you are getting while loading report to the table.

      Because if the blob is stored properly it can be fetched via mail or using application process to display on the page. It is a tested procedure.

    2. Mohamed sameer

      Thanks again, i will check that.

    3. Mohamed Sameer

      Hi Vinish,

      As you told I removed the exception part there is no error. The file perfectly stored in a table.

      But when I try to Fetch via mail or using application process to display on the page, I couldn't view the blob file.

      Please provide your suggestions.
      thanks.

    4. Mohamed sameer

      Yes Vinish, using Ajax application process itself giving me error. I really donno what’s exactly happen.

    5. Vinish Kapoor

      You must be missing something.

  2. esvk1965

    Vinish, great job. Thank You. You really helped me get past a brick wall today with displaying image with html.