Oracle Stored Procedure Data Set in BIRT

In BIRT reports it is possible to create a data set that is based on a stored procedure in the database.  In this post is discussed in detail how this needs to be done.  As an example I’ll show how to create a stored procedure in Oracle that meets the requirements to act as a data set and how to create the data set in BIRT.  As a bonus you’ll learn how to show data on a BIRT report from a temporary table.

Temporary table
First of all, create a temporary table in Oracle.  Feel free to skip this step if you are not interested in working with a temporary table.

CREATE GLOBAL TEMPORARY TABLE tmp_objects (object_name VARCHAR2(30), status VARCHAR2(20))
ON COMMIT PRESERVE ROWS;

As you can see, we have the ON COMMIT PRESERVE ROWS clause.  This is necessary because the default value of the auto-commit property of the JDBC datasource is set to TRUE (or auto, which also means TRUE).

If you really insist on having the ON COMMIT DELETE ROWS clause in the temporary table definition, or if you need to reuse an existing table that has this clause, you’ll need to change to auto-commit property to false.

Stored procedure
The stored procedure needs to return data in some way.  To serve as a data set in BIRT, an Oracle stored procedure needs to have an output parameter of type SYS_REFCURSOR.  The procedure in this example performs two steps:

• inserting all Oracle objects of a certain type in the temporay table
• open the cursor – select all records from the temporary table

CREATE OR REPLACE PROCEDURE refcur_test (
p_refcur OUT SYS_REFCURSOR,
p_type IN VARCHAR2
)
IS
BEGIN
-- pre-processing steps here
INSERT INTO tmp_objects (
object_name, status
)
SELECT o.object_name,
o.status
FROM all_objects o
WHERE o.object_type = p_type;
-- open the cursor
OPEN p_refcur FOR SELECT * FROM tmp_objects;
END;

BIRT data set
To create the data set in BIRT, start with selecting ‘SQL Stored procedure Query’ from the Data Set Type dropdown box, after clicking ‘new data set’

In the query area, make a call to the stored procedure. Don’t forget to mention the database owner in your call. Without it, things won’t work. (I’m not sure, but I think this is a JDBC issue)

Now create an output parameter to correspond with the refcursor parameter in the Oracle procedure.

And finally, take a look at the Preview Results to see if everything works fine. In my case, it does:

Et voila!

Re-using parameters in BIRT Data set

Today, a developer who is working on a large query to build a BIRT report came to me and asked if it was possible to specify data set parameters only once instead of having to specify a parameter value for EVERY question mark in the SQL statement even if the that same parameter is repeated a number of times in the statement.

This developer had 30+ question marks to bind, so naturally, he questioned this way of working.

There is, luckily, a workaround. Actually, there are 2 solutions: the first one is to create a stored procedure in the database and work with REF CURSOR, but the second solution is much easier and stays within BIRT; the WITH clause.

With the WITH statement you can define your parameters at the beginning of the statement and then reuse it.

Let’s suppose you have a parameter called YEAR which you use a number of times in your data set sql like so:

SELECT * FROM tab1 WHERE year = ?
UNION
SELECT *FROM tab2 WHERE year = ?

Here, we have two parameters we have to define twice in the data set parameter binding.

If we now rewrite the statement to use the WITH clause:

WITH
params AS
(SELECT ? AS year FROM dual)
SELECT * FROM tab1, params WHERE year = params.year
UNION
SELECT * FROM tab2, params WHERE year = params.year

As you can see we have moved the question mark to the WITH clause and as a result you only  have to specify and bind the parameter once. Make sure, though, that the WITH clause sql only generates 1 record otherwise you will make multiple rows.

Pfew, that’s saves a lot of monkey work…

PS: You can even create multiple paramlists like that. Suppose you have an additional filter on product consisting of 2 products to be filtered:

SELECT * FROM tab1 WHERE year = ? AND prod IN (?,?)
UNION
SELECT *FROM tab2 WHERE year = ? AND prod IN (?,?)

Rewrite it to use WITH:

WITH
params AS
(SELECT ? AS year FROM dual),
products AS
(SELECT ? AS prod FROM dual
UNION
SELECT ? AS prod FROM dual)
SELECT * FROM tab1, params WHERE year = params.year AND prod IN (SELECT prod FROM products)
UNION
SELECT * FROM tab2, params WHERE year = params.year AND prod IN (SELECT prod FROM products)