SQL Server Performance Forum – Threads Archive

Multiple Datasets against Oracle

I am new to Reporting Services and am running into an interesting problem. I want to do the following against an Oracle DB. I have complex logic than needs to be done on some data before the report is created. I created an Oracle Stored Procedure to populate an Oracle Temp Table with data. I want to first execute the Stored Procedure to generated a set of data and then run a query against it. The way Oracle Temp Tables work is that the data goes away after the session is ended. Therefore I need to execute the stored procedure first then run the sql statement against it. I can create a Reporting Services dataset as a Stored Procedure and execute it successfully. I can create a sql statement against the temp table and it will execute but come back with no data. My problem could be one of two things or a combination of them. 1) The two datasets are executing via different sessions. This means that the data is not available for the SQL statement to query. AND/OR 2) The stored procedure is not running and completing before the SQL statement runs.
Summary:
1) Where can I (if possible) set execution precedent for the datasets?
2) Can I force the dataset execution to occur within a single Oracle session? Thank You, Mike La Pointe

---

No expert on Oracle but if there is something similair to the temp tables in SQL Server (#local and ##global) you might have the option to make a global temp table that is accessible from different sessions.

---

The session thing is only 1/2 of the issue. Can I execute one of my Datasets first and not have the second one execute until the completion of the first?

---

Have you referred to the Reporting Services newsgroups set by Microsoft for this query and also I believe posting the question in Oracle forums will fetch you required information. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>