SQL Server Performance

Multiple Datasets against Oracle

Discussion in 'SQL Server Reporting Services' started by mlapoint, Apr 8, 2004.

  1. mlapoint New Member

    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
  2. Argyle New Member

    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.
  3. mlapoint New Member

    quote:Originally posted by Argyle

    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?
  4. satya Moderator

    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.

Share This Page