May 26, 2008 at 8:10 pm
I'm trying to build a report in SSRS 2005 using an already existing stoproc in Ora 10g.
The stored proc takes in IN parameters and outputs a record set through a IN OUT ref cursor as shown below -
CREATE OR REPLACE PROCEDURE PBICS.SP_COMP_RPTS
(p_REPORT_TYPE IN VARCHAR2,
p_FLAG IN VARCHAR2,
p_MARKET IN VARCHAR2,
p_ASSOC_ID IN NUMBER,
p_DATE_TYPE IN VARCHAR2,
p_DATE IN DATE,
p_NBKID IN VARCHAR2,
PROCEDURERESULTSET OUT Report_Ref_Cursor.REPORTREFCURSORTYPE
)
AS
I'm facing the following issues -
a) I created a dataset in SSRS using this stoproc and ran it by passing in the input parameters BUT I get NO DATA in the recordset even though I could see the record structure i.e it has all the columns that stoproc returns.
b)For a another stoproc I could get DATA in the dataset but I got ONLY a single row even though for the parameters passed into the stoproc I should be getting multiple rows through OUT ref cursors.
Is there ANY problem with Oracle storpoc being used for SSRS reporting?
Is Oracle 10g supported well in SSRS SQL 2005 ?
Are any of 10g features causing the dataset to NOT return any rows?
Will be very appreciative if any one can solve these issues I'm facing.
Thanks in advance...
May 26, 2008 at 9:38 pm
Hi I am trying to do the same thing. My test proc accepts a number and then has the out ref cursor. Using SSRS it says that I am getting a wrong number of arguments or types. Could you post the text exactly as you have in the SSRS data window?
May 27, 2008 at 7:51 am
I first created a datasource for the oracle DB by selecting "Oracle" as datasource type.
I could not use ODBC may be because of Oracle 10g.
Created a new dataset where I selected the command type as - Stored procedure and in the query stirng I gave the following - schema.storedproc_name.
You can also give the storedproc name in query window after selecting command type as "stored procedure".
like as below
PBICS.Sp_Comp_Statement_Detail_001
May 28, 2008 at 8:58 am
What are you using for connectivity? IIRC, the MS provided Oledb and ODBC drivers are built as Oracle 7 API clients, and they have some significant limitations. It sounds like you are.
I'd use anything BUT them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply