Including Dynamic Images in Your Report

Purpose

This tutorial shows you how to include dynamic images in your report.

Time to Complete

Approximately 15 minutes

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Adding and Loading a BLOB Column
	Creating a BLOB2CLOB Function
	Creating a Report Query and Report Layout
	Invoking the Report from an Application Page
	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 dynamically include images in your application. In this tutorial, you create a report query and layout that dynamically includes images in a PDF report.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

Perform the Creating a PDF Report with Multiple Queries tutorial.

Back to Topic List

Adding and Loading a BLOB Column

In this section, you alter the OEHR_EMPLOYEES table and add a BLOB column and load it with data. Perform the following steps:

1.	Open a DOS command prompt and execute the addblobdata.sql script (you may need to modify this file and change the location of the directory). cd \<directory where you put zip files> sqlplus <schema name>/<schema password> @addblobdata
2.	To view the data in the OEHR_EMPLOYEES table, 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
3.	To log in to Oracle Application Express, enter the following details, and click Login. Workspace: <your workspace name> Username: <your username> Password: <your password>
4.	On the Workspace home page, click SQL Workshop.
5.	Click Object Browser.
6.	Select OEHR_EMPLOYEES from the list of tables.
7.	Notice that the additional BLOB columns are now listed. Select the Data tab.
8.	Scroll left to see the data for employees 101 and 103.
9.	Notice that there is data for the FILENAME and MIMETYPE columns.

Back to Topic List

Creating a BLOB2CLOB Function

In order to include the BLOB in your PDF report, the data needs to be converted to CLOB base 64 encoded format. You will create a function to perform the conversion. Perform the following steps:

1.	Select SQL Scripts from the list of Components.
2.	Click Upload.
3.	Click Browse... and select the blob2clobase64.sql file (located where you downloaded and unzipped the prerequisite files) and click Open.
4.	Click Upload.
5.	Click the script icon.
6.	Click Run.
7.	Click Run to confirm.
8.	Click the View Results icon.
9.	The blob2clobase64 function was created successfully.

Back to Topic List

Creating a Report Query and Report Layout

In this section, you create a Report Query and a Report Layout. Perform the following steps:

1.	Navigate to the Application Builder. Select the Printing Application.
2.	Click Shared Components.
3.	Under Reports, click Report Queries.
4.	Click Create.
5.	Enter blobquery for the Name and click Next.
6.	Enter the following query and click Next. Notice that the SQL statement invokes the function you created in the previous section to convert the BLOB column PHOTO to CLOB based 64 encoded format. select employee_id, first_name\|\|' '\|\|last_name name, filename, mimetype, blob2clobase64(photo) photo from oehr_employees where photo is not null
7.	Your query was created. Click Next.
8.	Click Browse... and select the bloblist2.rtf file (located where you downloaded and unzipped the prerequisite files) and click Open.
9.	Click Next.
10.	If you haven't done so already, you need to first start BI Publisher. Double-click the Start BI Publisher icon on your desktop
11.	Click Test Report.
12.	Click Open.
13.	The report is shown. When done viewing the report, close the window.
14.	Click Finish.
15.	Your Report Query and Report Layout were created successfully. In the next section, you create a button on your page to run the report. Select your Application breadcrumb.

Back to Topic List

Invoking the Report from an Application Page

In this section, you invoke the report query from a page in your application. Perform the following steps:

1.	Navigate to your Printing Application page. Click the Home page.
2.	Click the Create Button icon.
3.	Select the Home region and click Next.
4.	Accept the default and click Next.
5.	Enter Print Report with Images for Button Name and select Download printable report query for Action and click Next.
6.	Accept the default and click Next.
7.	Select [Top] from the list of quick links or select Top of Region from the drop down list and select Left for Alignment and click Next.
8.	Select blobquery for the Report Query and click Create Button.
9.	Your button was created successfully. Click Run.
10.	You may be asked to login to the application. Enter your username and password and click Login.
11.	Click Print Report With Images.
12.	Click Open to open the PDF report.
13.	The report is displayed.

Back to Topic List

Summary

In this tutorial, you learned how to:

	Add and load a BLOB column
	Create a function that performs the BLOB to CLOB based 64 encoded format conversion
	Create a report query and layout
	Invoke the report from an application page

Back to Topic List