Creating a Report Using the JDBC Pluggable Data Source
Creating a Report Using the JDBC Pluggable Data Source
The goal of this tutorial is to show you how to build a report
using the JDBC pluggable data source (PDS).
Approximately 1 hour
This tutorial covers the following topics:
Place
the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: Because this action loads all screenshots simultaneously, response
time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor
over each individual icon in the following steps to load and view only the screenshot
associated with that step.
The information you must publish is often derived from data
in various corporate data sources. These data sources may be SQL-based (relational
databases) or non-SQL-based. Often, you must combine data from one or more of
these data sources to publish meaningful information. For example, you may need
to combine data that exists in a relational database with data from a multi-dimensional
database to compare trends and performance.
Using Oracle Reports, you can access any Oracle or non-Oracle
data source, such as XML, JDBC (relational and non-relational), metadata from
another system, or even a Business Component for Java (BC4J). You can even combine
data from one or more of these data sources in the same report. For example,
in a business intelligence scenario, you can combine and publish data that exists
in a relational database along with multi-dimensional (OLAP) data, allowing
the comparison of trends and performance. In a Business-to-Business supplier
scenario, you can join XML data obtained from an external partner’s application
with data obtained from an internal database and merge this internal and external
data in real-time to display the results in a Web page.
A pluggable data source (PDS) is an Oracle Reports feature
that enables data input from numerous sources through the implementation of
design time and runtime Java application program interfaces (APIs). Oracle Reports
provides interface definitions, which act as a translator between Oracle Reports
and a PDS by redefining Oracle Reports' requests in terms your data source uses.
You can leverage the PDS architecture to connect to your own data source, as
well as to the data sources available with Oracle Reports. The PDS feature also
enables you to leverage various capabilities in Oracle Reports such as aggregation,
summarization, formatting, and scheduling not only data that is accessed through
SQL, but on data from any data source.
Java Database Connectivity, or JDBC, is a Java class library
that provides a common way to maintain a connection to a database and enables
you to access virtually any tabular data from the Java programming language.
This provides you with cross-DBMS connectivity to a wide range of databases
and other tabular data sources, such as spreadsheets and flat files. With a
JDBC technology enabled driver, you can easily integrate all corporate data,
even in a heterogeneous environment.
Back to Topic List
In this tutorial, you will create a report displaying
the product inventory in warehouses located in various countries around the
world. In the first part of the tutorial, you will create a query in the data
model to retrieve warehouse and inventory data from the
OE schema in the Oracle 10g database. Then, using the JDBC pluggable
data source, you will connect to the HR schema of the same database and add
another query to the data model of your report. This query will retrieve location
information for each warehouse. You will then link the two queries in the data
model, define the layout for the report, and generate the report output to a
PDF file.
Back to Topic List
Before starting this tutorial, you should:
| 1. |
Have access to or have installed Oracle
Reports Developer. |
| 2. |
Have access to or installed the sample
schema. This tutorial uses the OE and HR schemas included in the Oracle10g
database. |
| 3. |
Download reportsOBE.zip
and unzip into your working directory. |
Back to Topic List
To begin this tutorial, you will create a new report module
and define a SQL query in the data model.
Back to Topic List
| 1. |
If it is not already open, start Oracle Reports by clicking
the Reports Builder icon on your desktop. The welcome
screen displays. Select the option Build a new report manually.
Click OK.
If Reports Builder is already open, create a new report by selecting
File > New > Report from the menu.
Select the option Build a new report manually. Click
OK.
In both cases, the Data Model view of the Report Editor displays.
|
| 2. |
The Data Model view is a work area in which you create,
define, and modify data model objects to be used in your report. Data
model objects include queries, groups, columns, parameters, and links.
Reports Builder uses the data model to determine what data to retrieve
for the report.
Add a query to the data model by clicking the SQL query icon 
on the tool palette and click anywhere in the empty work area. This
action invokes the SQL Query Statement dialog box.
|
| 3. |
Click Connect to connect to the database.
In the Connect dialog box, enter OE for the User Name,
OE for the Password, and the name of your database
for the Database field.
|
| 4. |
You can now type in a SQL statement, define a query visually
using Query Builder, or import an existing file.
For your convenience, a SQL query is provided for you. Click Import
SQL Query.
In the file import dialog box, click OEquery.sql
located in the your working directory. Click Open.
The SQL statement is displayed for you.
Click OK.
The warehouse query is created and represented graphically in the Data
Model view.
|
| 5. |
Your data model contains a single group.
With this structure, each row of data retrieved for the report will
display with the same frequency.
For this report, you want to create a hierarchy in the data model so
that the data for each warehouse displays only once, followed by several
inventory records displaying product data. To achieve this, you need
to create two groups in the data model.
Click and drag product_id, product_name,
and quantity_on_hand down into a new group.
Your data model now contains two groups, G_warehouse_name and G_product_id.
|
Back to Topic List
Now you will add warehouse location information to the data
model. This data is stored in the HR schema of the Oracle 10g database. You
cannot connect to another schema or another Oracle database using a SQL connection
in the same report. You will create a JDBC connection to the HR schema using
a preconfigured JDBC driver.
The JDBC pluggable data source is preconfigured with three
JDBC drivers:
 |
Oracle Thin JDBC driver |
|
Oracle Thick JDBC driver |
|
Javasoft JDBC-ODBC bridge |
Merant JDBC drivers are supplied with Oracle Application Server
for accessing other data sources. If you wish to use these drivers with the
JDBC PDS, refer to the Oracle Application Server Installation Guide
and the Oracle Application Server Reports Services Publishing Reports to
the Web manual available on Oracle Technology
Network.
In this tutorial, you will use the Oracle Thin JDBC driver.
Back to Topic List
| 1. |
Click the JDBC Query icon 
on the tool palette and click anywhere in the Data Model work area.
This action invokes the JDBC Query dialog box.
Here you define a JDBC query or stored procedure, and the connection
parameters for your data source.
|
| 2. |
Define the JDBC query.
For your convenience, the code is provided for you in the file HRquery.sql.
Open this file from your working directory using a text editor, such
as Notepad or WordPad.
Select all the code in this file by using Ctrl+A,
and copy it by using Ctrl+C, or by selecting Edit
> Copy from the menu.
Return your focus to the JDBC Query dialog box and paste the code in
the Query/Procedure window by using Ctrl+V.
|
| 3. |
Next, specify the parameters that the query will use
to connect to the database.
Make sure that the sign-on parameter field contains the default value
P_JDBCPDS.
Click Connect to display the JDBC Query Connect dialog
box.
Connect to the HR schema in the Oracle 10g database. Enter HR
for the User Name and HR for the Password. For the
Database field, enter <hostname>:<port>:<SID>
for your database. You can get this information from your tnsnames.ora
file, or from your database administrator. Finally, select oracleThin
for the Driver Type field.
Click Connect. Click OK.
You've created the second query in your data model.
|
| 4. |
Now you will link these two queries by using the common
field, location_id.
Click the Data Link icon 
on the tool palette. Click LOCATION_ID1 in the data
model group QP_1 and create a link by dragging your mouse to location_id
in the data model group Q_1. Your data model should look like this:
|
| 5. |
Save your report as WarehouseRpt_<your_initials>.jsp.
To save a report definition, select the report module in the Object
Navigator window and click the Save icon 
in the toolbar
, or select File > Save from the menu. The first time you
save your report definition, the Save dialog box displays, giving you
the opportunity to enter a meaningful name for your report.
|
Back to Topic List
You have successfully defined the data model for your warehouse
report. Your next step is to define the layout for the data model. You will
use the Report Wizard to do this.
Back to Topic List
| 1. |
Select Tools > Report Wizard
from the menu. This action invokes the Report Wizard.
On the Report Type tab, accept the default option to Create
both Web and Paper Layout by clicking Next.
|
| 2. |
On the Style tab, select Group Above
as the report style and enter Warehouse Report as
the title.
Click Next.
|
| 3. |
On the Groups tab of the Report Wizard, display all
groups in your report.
Select G_COUNTRY_NAME in the Available Groups list
and click the [Down >] button. The field is now
displayed on the right in the Displayed Groups list. Repeat this process
for G_warehouse_name and G_product_id.
Click Next.
|
| 4. |
Now specify the fields to display in your report. From the Available Fields
list, select COUNTRY_NAME and click the [>] button.
Repeat this process for warehouse_name, product_id,
product_name, and quantity_on_hand fields.
Ensure that the displayed fields are in the order
shown below. You can drag and drop a field to modify the order.
Click Next.
|
| 5. |
The wizard now allows you to modify the default labels
and widths. For this tutorial, keep the defaults as they are.
Click Next.
|
| 6. |
Keep the default setting for the template. Click Finish
to complete the definition of your report. You now view the
paper layout for your report in the Paper Design view of the Report
Editor.
Save your report definition by clicking Save .
|
Back to Topic List
Reports Builder can generate report output
to a PDF file, containing the formatted data and all objects. When you generate
your report output to a PDF file, you can distribute the output to any PDF destination,
including e-mail, printer, OracleAS Portal, and Web browser.
Back to Topic List
| 1. |
Select WAREHOUSERPT_<your initials>
in the Object Navigator.
|
| 2. |
Select File > Generate to File > PDF from
the menu. Save your report output as WarehouseRpt _<your
initials>.pdf, such as WarehouseRpt_pm.pdf.
Click Save.
|
Back to Topic List
In this lesson, you've learned how to:
|
Define a query in
the data model using the SQL query data source |
|
Add a second query to the data model using
the JDBC pluggable data source |
|
Create a layout for the report |
|
Generate report output to a PDF file |
Back to Topic List
|
To learn more about
Oracle Reports 10g, refer to the additional Reports OBEs. |
|
Visit the OTN
Web site for technical papers, presentations, frequently asked questions,
demonstrations, and more. |
Back to Topic List
Place
the cursor over this icon to hide all screenshots.
|
Bookmark
