Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 24th, 2004, 01:35 PM
Authorized User
Join Date: Jun 2004
Location: , , .
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?
  #2 (permalink)  
Old August 27th, 2004, 10:22 AM
Registered User
Join Date: Aug 2004
Location: , , .
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.

  #3 (permalink)  
Old October 1st, 2004, 09:35 AM
Registered User
Join Date: Oct 2004
Location: , , .
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.

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 02:18 AM.

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