How to Load BI Publisher Report Content to a BLOB Field in 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).

2. 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.
3. 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:

• How to Call BI Publisher Report from Oracle Apex?
• How to Embed BI Publisher Report into a Page in Oracle Apex?
• Oracle Apex Dynamic Popup Navigation Menu Example