Storing and Accessing Reports in the Database

Purpose

This tutorial shows you how to store and access a report in the database.

Time to Complete

Approximately 15 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating a Table to Store Your Reports
 Generating and Storing a Report in the Database
 Accessing the Stored Report in the Database
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Beginning with Oracle Application Express 3.1, you can store a report in a BLOB column in your database. In this tutorial, you will create a new table to store the reports that are generated and then create a report of the contents of the table.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Creating a PDF Report with Multiple Queries tutorial.

2.

Perform the Including Dynamic Images in Your Report tutorial.

Back to Topic List

Creating a Table to Store Your Reports

In this section, you upload and run a script that creates a table to store the reports that you generate. Perform the following steps:

1.

Open your browser and enter the following URL to log in to Oracle Application Express (change the <hostname> to localhost, your specific hostname or apex.oracle.com).

http://<hostname>:8080/apex

 

2.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

3.

Click the SQL Workshop tab. Then click SQL Scripts.

 

4.

Click Upload.

 

5.

Click Browse... and select the report_archive_tbl.sql file (located where you downloaded and unzipped the prerequisite files) and click Open.

 

6.

Click Upload.

 

7. Click the report_archive_tbl.sql script icon.

 

8.

Click Run.

 

9.

Click Run to confirm.

 

10.

Click the View Results icon.

 

11.

The report_archive table was created successfully.

 

Back to Topic List

Generating and Storing a Report in the Database

In this section, you create a process that stores the report in the database. Perform the following steps:

1.

Navigate to the Application Builder. Select the Printing Application.

 

2.

Click the Home page.

 

3.

You need to create a process that will store the report in the database. Under Processes, click the Create icon.

 

4.

Select PL/SQL and click Next.

 

5.

Enter store_blobquery_report for the Name and click Next.

 

6.

Enter the following code in the PL/SQL Page Process area and click Next.

                               
declare
                                
l_report blob;
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
);
end;

 

7.

Click Next.

 

8.

Select PRINT_REPORT_WITH_IMAGES for When Button Pressed and click Create Process.

 

9.

You also want to create a process to store the Multi Query report. Under Processes, click the Create icon.

 

10.

Select PL/SQL and click Next.

 

11.

Enter store_multiquery_report for the Name and click Next.

 

12.

Enter the following code in the PL/SQL Page Process area and click Next.

                               
declare
                                
l_report blob;
begin
l_report := apex_util.get_print_document (
p_application_id => :APP_ID,
p_report_query_name => 'multiquery',
p_report_layout_name => 'multiquery',
p_report_layout_type => 'rtf',
p_document_format => 'pdf'
);
insert into report_archive (
filename,
mimetype,
report,
created_date,
created_by
) values (
'Multi Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')',
'application/pdf',
l_report,
sysdate,
:USER
);
end;

 

13.

Click Next.

 

14.

Select PRINT_REPORT for When Button Pressed and click Create Process.

 

15.

Click Run.

 

16.

Click Print Report With Images. When the dialogue to open or save the report appears, you can click Cancel.

 

17.

Click Print Report. When the dialogue to open or save the report appears, you can click Cancel.

Note: Two rows were inserted into the database for each report you ran.

 

Back to Topic List

Accessing the Stored Report in the Database

In this section, you create a report of the data in the report_archive table in the database. Perform the following steps:

1.

Navigate to your Printing Application page. Click the Home page.

 

2.

Under Regions, click the Create icon.

 

3.

Select Report and click Next.

 

4.

Make sure SQL Report is selected and click Next.

 

5. Enter List of Stored Reports for Title and click Next.

 

6.

Enter the following SQL in the SQL Query area and click Create Region.

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

 

7.

The report was created. You need to make a few changes to the Report Attributes. Under Regions, select the Report link.

 

8.

Under Headings Type, click Custom. Change the name of the Report Length header to Report. Deselect the Show check box for MIMETYPE. You also want to create a link to download the report in the Report Length column. Select the edit icon for Report Length.

 

9.

Under Column Formatting, enter the following string in the Number / Date Format field and click Apply Changes.

                               
DOWNLOAD:REPORT_ARCHIVE:REPORT:ID::MIMETYPE:FILENAME:::inline:[Download]
                            

Note this string contains the following parameters:

<Format Mask>:<BLOB Table>:<BLOB Column>:<Primary Key Column 1>::<MIME Type Column>:
<Filename Column>:::<Content Disposition>:<Download Link Text>

 

10.

Click Run Page.

 

11.

The list of reports that you ran in the previous section of this tutorial are in the list. To view a report, click the [Download] link.

 

12.

The report is displayed successfully.

 

Back to Topic List

 

Summary

In this tutorial, you learned how to:

 Create a table to store your reports
 Generate and store a report in the database
 Access the stored report in the database

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

Left Curve
Popular Downloads
Right Curve
Untitled Document