SSRS Report from Oracle DB - Use stored procedure

Question

I am developing a report in Sql Server Reporting Services 2005, connecting to an Oracle 11g database. As you post replies perhaps it will help to know that I'm skilled in MSSQL Server and inexperienced in Oracle.

I have multiple nested subreports and need to use summary data in outer reports and the same data but in detail in the inner reports. In order to spare the DB server from multiple executions, I thought to populate some temp tables at the beginning and then query just them the multiple times in the report and the subreports.

In SSRS, Datasets are evidently executed in the order they appear in the RDL file. And you can have a dataset that doesn't return a rowset. So I created a stored procedure to populate my four temp tables and made this the first Dataset in my report. This SP works when I run it from SQLDeveloper and I can query the data from the temp tables. However, this didn't appear to work out because SSRS was apparently not reusing the same session, so even though the global temporary tables were created with ON COMMIT PRESERVE ROWS my Datasets were empty.

I switched to using "real" tables and am now passing in an additional parameter, a GUID in string form, uniquely generated on each new execution, that is part of the primary key of each table, so I can get back just the rows for this execution.

Running this from Sql Developer works fine, example:

DECLARE
ActivityCode varchar2(15) := '1208-0916      ';
ExecutionID varchar2(32) := SYS_GUID();
BEGIN
CIPProjectBudget (ActivityCode, ExecutionID);
END;

Never mind that in this example I don't know the GUID, this simply proves it works because rows are inserted to my four tables.

But in the SSRS report, I'm still getting no rows in my Datasets and SQL Developer confirms no rows are being inserted.

So I'm thinking along the lines of:

• Oracle uses implicit transactions and my changes aren't getting committed?
• Even though I can prove that the non-rowset returning SP is executing (because if I leave out the parameter mapping it complains at report rendering time about not having enough parameters) perhaps it's not really executing. Somehow.
• Wrong execution order isn't the problem or rows would appear in the tables, and they aren't.

I'm interested in any ideas about how to accomplish this (especially the part about not running the main queries multiple times). I'll redesign my whole report. I'll stop using a stored procedure. Suggest anything you like! I just need help getting this working and I am stuck.

If you want more details, in my SSRS report I have a List object (it's a container that repeats once for each row in a Dataset) that has some header values and then contains a subreport. Eventually, there will be four total reports: one main report, with three nested subreports. Each subreport will be in a List on the parent report.

Answer 1

Sigh.

The column being selected in the SP has a char data type, but the SP had a varchar2 input parameter for it. In Oracle, varchar2 variables actually lose their trailing spaces (unlike in SQL Server). On top of that, I was doing RTrim() on the SSRS parameter as queried from the database.

For those who might want to know... SPs in Oracle can't return rowsets. So if you're running an SP from SSRS it's presumably to populate tables. But SSRS by default usually runs queries simultaneously. To make it run queries serially, edit the dataset that runs the SP, click the "..." button next to the "Data source" dropdown, and check the "Use single transaction" checkbox at the bottom. As long as the SP is the first dataset listed in the RDL file, it should run first and the other datasets wait until it has finished. Note: this setting applies to all datasets using the same data source.