Wrox Programmer Forums
BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7
This is the forum to discuss the Wrox book Professional SQL ServerReporting Services by Paul Turley, Todd Bryant, James Counihan, George McKee, Dave DuVarney; ISBN: 9780764568787
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 24th, 2004, 01:35 PM
Authorized User
Join Date: Jun 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calling an Oracle Stored Procedure

We have a data warehouse on Oracle from which we need to produce a reporting package using SQL Server Reporting Services. We have already produced one package where a Data Transform from Oracle to SQL Server was used and stored procedures accessed data for the SQL Server Reporting Services reports- no problem. I'm looking to see if there are any alternatives to this method such as the Reporting Services report dataset calling an Oracle stored procedure directly. I have managed to configure a dataset to Oracle and to extract data into a report using the DataSet editor Query String however the report users need the ability to select run criteria and so pass to parameters to the Oracle query. Any advice much appreciated?
Old August 27th, 2004, 10:22 AM
Registered User
Join Date: Aug 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Went through this very same problem! Found nothing on the Net to help either! so after much trial and error...

1. Set up an OLE_DB connection in RS pointing to your Oracle database.
2. I use the Report Wizard, but this will work either way! When the box comes up for you to input a SQL Statement, type Select * From Dual. You will be getting rid of this soon, but it's the only way to get past that screen. Once you've clicked the finish button your report should open in RS. Click on Data tab, then on Generic Query Designer (looks like 2 tables with a pencil running diagonal). Now in the SQL Window type {call <insert procedure name>()}. This will call a procedure in Oracle say if you want to get a list of values for a parameter box.

3. If you need to pass in parameters:
  again type {call <insert procedure name>()}, but now go to the Layout Tab, create whatever parameters you need from the user. Now go back to the Data tab and change {call <insert procedure name>(?,?,?,?)}. Oracle requires unnamed parameters thus the ?. Also if you have 1 parameter then you would have 1 ?, etc. Now click on ... next to your datasource. Click on the Parameters tab, now for each parameter you just specified you would look like the following
? =Parameters!whatever.value

Click OK, Refresh to get your datafields populated. Click ! to run, you will be prompted to input parameters. Data should now show up.

Now you can go to Layout and using the Fields drag and drop them onto your layout, click preview, you will enter parameters and ta da you should have a report that is driven by an Oracle Stored Procedure.

Old October 1st, 2004, 09:35 AM
Registered User
Join Date: Oct 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts

Ok, I can't get this to work. Here is my situation.

Table has three columns, SOURCE_SYS (Either 'A' or 'B') REPORT_ID and REPORT_NAME, both are varchar2.

Package Header:
create or replace package PKG_TEST_RS is
TYPE tReport_ID is TABLE of rpt_reports.report_id%TYPE
TYPE tReport_Name is TABLE of rpt_reports.report_name%TYPE

PROCEDURE getRptReport
         (vInList IN rpt_reports.source_sys%TYPE,
            Report_ID OUT tReport_ID,
            Report_Name OUT tReport_Name);


Package Body:
create or replace package body PKG_TEST_RS is

PROCEDURE getRptReport
         (vInList IN rpt_reports.source_sys%TYPE,
            Report_ID OUT tReport_ID,
            Report_Name OUT tReport_Name)
    CURSOR cReports IS
     SELECT rr.report_id,
        FROM rpt_reports rr
        WHERE rr.source_sys = vInList;


    FOR tmpReports IN cReports
     Report_ID(cReports%ROWCOUNT) := tmpReports.Report_ID;
     Report_Name(cReports%ROWCOUNT) := tmpReports.Report_Name;



R/S Info
OLEDB Connection

I tried to use the following "queries":

{ call PKG_TEST_RS.getRptReport('B') }
This one doesn't work because I get the wrong number of parameters

I don't know how to set up the parameters coming out of the Stored Procedure as Fields withing Reporting Services. I tried setting them up as Database Fields and Calculated Fields in the Dataset editor but it doesn't seem to work.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling Oracle Stored Procedure using Linked Serve Twinklestar SQL Server 2000 4 September 23rd, 2009 04:02 AM
calling stored procedure jomet JSP Basics 0 November 23rd, 2007 08:06 AM
calling an oracle procedure from a jsp page jasonteaboy Pro JSP 0 November 20th, 2006 03:06 PM
Calling Oracle Stored Procedures booksnore2 General .NET 0 August 24th, 2004 02:08 PM
CALLING ORACLE SELECT PROCEDURE danielwajnberg Oracle 1 November 20th, 2003 09:36 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.