SSRS executing a Stored Procedure in Oracle...

Question

Hello...I have a Report in SSRS VS2008 that needs to run a Stord Procedure from Oracle. In the past I have run functions from Oracle that return tables in order to display the data. As well as straight forward SELECT statements

For example:

select * from table(MyFunction(:parm1, :parm2))
select * from MyTable

I have not run a Stored Procedure from Oracle in SSRS and I am wondering....

How do I define the Query in the DataSet to return the results?

Answer 1

Create a stored procedure with an output parameter of type SYS_REFCURSOR, and place the results of the query in the output cursor within the procedure - like so:

CREATE OR REPLACE PROCEDURE pr_myproc(L_CURSOR out SYS_REFCURSOR) 
is
   BEGIN
     OPEN L_CURSOR FOR
       SELECT * FROM mytable;
   END;

Then create the dataset in your report - on the Query tab, set the Command type to StoredProcedure, and the Query string to the name of the procedure, eg. pr_myproc. If you check the fields tab, these should match the expected structure of the cursor, ie. the query output.