SSRS Report from Oracle DB - Using a Stored Procedure

Question

I am developing a report in Sql Server Reporting Services 2005 by connecting to an Oracle 11g database. As you post your answers, maybe this will help you find out that I own MSSQL Server and inexperienced in Oracle.

I have several nested subscriptions and you need to use summary data in external reports and the same data, but in detail in internal reports. To save the database server from several executions, I thought that I would first remember some temporary tables, and then query them several times in the report and sub-reports.

In SSRS, data sets are obviously executed in the order in which they appear in the RDL file. And you may have a dataset that does not return a rowset. So I created a stored procedure to populate four temporary tables and made this the first data set in my report. This SP works when I run it from SQLDeveloper, and I can query data from temporary tables. However, this did not seem to work because the SSRS did not seem to reuse the same session, therefore, although the global temporary tables were created using ON COMMIT PRESERVE ROWS, my datasets were empty.

I switched to using "real" tables, and now I pass an additional GUID parameter in the form of a string, uniquely generated with each new execution, which is part of the primary key of each table, so I can only return rows for this execution.

Running this from Sql Developer works great, for example:

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

Do not forget that in this example I do not know the GUID, it just proves that this works because the rows are inserted into my four tables.

But in the SSRS report, I still don't get any rows in my datasets, and the SQL developer confirms that the rows are not inserted.

So, I think line by line:

• Oracle uses implicit transactions, and my changes fail.
• Despite the fact that I can prove that the SP, which performs non-line typing, is running (because if I do not indicate the display of parameters, it complains about the time of rendering the report that it does not have enough parameters), maybe it really doesn’t performed. Somehow.
• Incorrect execution order is not a problem, or rows will appear in tables, but it is not.

I am interested in any ideas on how to do this (especially in the fact that the basic queries were not executed several times). I will redesign my entire report. I will stop using the stored procedure. Offer anything you want! I just need help with this and I'm stuck.

If you want more detailed information, in my SSRS report I have a List object (this is a container that repeats once for each row in the data set), which has some header values ​​and then contains a subheading. In the end, there will be four general reports: one main report with three nested sub-reports. Each subordinate will be listed in the parent report.

Answer 1

Sigh.

The column selected in SP has a char data type, but SP has an input parameter varchar2 for it. In Oracle, varchar2 variables actually lose their trailing spaces (unlike SQL Server). In addition, I did RTrim() for the SSRS parameter, which was requested from the database.

For those who want to know ... SP in Oracle cannot return rowsets. So if you use SP from SSRS, it will probably populate the tables. But SSRS, by default, usually executes requests at the same time. In order for it to execute queries sequentially, so that the subsequent query can properly depend on the result of the previous one, edit the data set that the SP is running on, click the "..." button next to the "Data Source" drop-down list and check the "Use one transaction" checkbox below . While SP is the first dataset listed in the RDL file, it must be started first, and the rest of the datasets will wait until it completes. Note. This option applies to all datasets using the same data source.