Reporting services can create data sets on top of Oracle stored procedures. Sure, the manual says so, but this afternoon, I tried to get things up and running, and I want to share some of the dead ends and problems I experienced in that, maybe it can help you avoid these obstacles. Don’t panic, not all paths are dead ends, we got to the stored procs in Oracle via Reporting Services .

Please note that I’m not an oracle expert, nor did I spend days figuring these things out. So please, if you can share your experience, please do so!

Getting started

There are two important decisions that can cause this querying to go wrong:

1. Which data provider do we use?
2. How do we create the procedure in Oracle

First the data provider. I installed Oracle 10g Express edition on the same machine that was running Microsoft Reporting Services 2008 R2. To test the connection with Oracle independent of the Microsoft product stack, I also installed the freeware version of Toad. A first frustration that I ran into was that I was unable to connect to Oracle from Toad on my 64 bit version of windows 7. I could access Oracle from the web interface, but not from Toad. Installing the client tools for Oracle express didn’t help .

So I moved to 32 bit box of Win7. Installing Oracle Express with client tools on this box and… hurray, Toad can talk to Oracle. Next, I used Integration Services to push the DimGeography table from AdventureworksDW into my Oracle.

Time to query the data from Reporting Services. I first try sending an explicit query with the three data providers that I have on my machine:

1. Microsoft OleDb Provider for Oracle (MSDAORA)
2. Oracle Provider for OLE DB (OraOLEDB)
3. Oracle (OracleClient .Net)

MSDAORA

Configuring the data provider is straight forward, only remember to not use “.” or “(local)” as the name of the local machine, but use localhost (or 127.0.0.1) instead.

When I create a dataset with query type Text, I can start the graphical query builder. The query builder lists nicely all tables and views, and is clever enough to put double quotes around my object names.

Also query type Table is available, but unfortunately, this one does not list the available tables, I must type the table name myself. Don’t forget to put double quotes around the table name, or Oracle can become angry at you!

Query type Stored Procedure is available as well, but whatever I tried, I could not get it working. Nothing shows up in the drop-down box, typing the procedure name  results in an error asking for parameters, when I start adding parameters in the call as well, it either claims
ORA-06550 PLS-00201 identifier must be declared, or
ORA-06550 PLS-00306 wrong number or types of arguments, or
ORA-01008 not all variables bound

I gave up on this one.

OraOLEDB

Also easy to configure this Oracle OleDb driver, just as MSDAORA. When building a query in the graphical query builder environment, the query builder doesn’t put double quotes around table and column names, which causes error
ORA-00942: table or view does not exist.
Of course, we can manually add the quotes, but it’s nice if computers work for us, not the other way around. Besides this, the driver seems to give the same results as MSDAORA, and I can’t seem to get the stored procedure calls up and running. Next!

OracleClient

Last one down the list. Configuring the DataSource is comparable with the two previous: local connections should refer to Localhost or 127.0.0.1, not just “.”. As query type, only Text and Stored procedure is available, not Table. The Text graphical query builder behaves as with MSDORA.

But the very good news is that if you create an Oracle stored procedure in the proper way (more on that in a second), that you can select the stored procedure radio button and the drop-down box will be populated with the Oracle stored procedures! Just select one from the list and it will behave exactly as on sql server: All the regular parameters of the stored procedure become report parameters, and a data set is built based on the first table returned by the stored procedure.

Building the Oracle stored procedure

Oracle is somewhat different from SQL Server when it comes to stored procedures. In Oracle you can create a package, a container in which you can declare and implement variables, stored procedures, functions,…. In contrast to SQL Server, a stored procedure in Oracle cannot return a table, only functions can do so. But … we still need to use the stored procedure anyhow, if we want it to show up in the Reporting services drop-down box. The trick is to make sure this stored procedure builds up the result in a REF CURSOR, and make this ref cursor a parameter! To illustrate this, I show you the code to get a list of all cities in a particular country (a lot of thanks to Philippe Clercq for writing this!)

CREATE OR REPLACE PACKAGE sp_country AS  
  type ref_cursor is REF CURSOR;  
  PROCEDURE get_city(country_name IN varchar2 default null,po_cursor out ref_cursor);
END sp_country;

CREATE OR REPLACE PACKAGE sp_country AS

PROCEDURE get_city(country_name IN varchar2,po_cursor out ref_cursor)  IS    
  BEGIN
  open po_cursor for SELECT
"GeographyKey", "City", "StateProvinceCode",
   "StateProvinceName", "CountryRegionCode", "EnglishCountryRegionName",
   "SpanishCountryRegionName", "FrenchCountryRegionName", "PostalCode",
   "SalesTerritoryKey"
FROM DW."DimGeography" g
where upper(g."EnglishCountryRegionName") like upper(country_name||'%'); 
    END;
END sp_country;

And with that, we could finally build our Reporting Services report on top of Oracle Express Edition (XE) and have the stored procedures show up in the drop down box. Of course, once your data is in, it doesn’t matter anymore whether you build your reports on top of SQL Server or Oracle.

Nico