greenmtnsun

Keith Ramsey's Business Intelligence Blog

Simple Stored Procedure to return data to a SSRS Report from Oracle

If you are like me, SQL Server is a breeze to write a Stored Procedure against and Oracle isn't as intuitive.  I had a report that simply needed to do the following:

1. Run a query in Oracle.

2. Return the data to Reporting Services so that it could report on it.

3. I couldn't link through SQL Server.

Its really simple code, but different than the way we do it on the SQL Server side.  In SQL Server you just do a Select at the end of your SP and it gets presented to Reporting Services, but Oracle needs to use a Cursor. I scoured the internet real quick to find an example SP like I can find for all kinds of things in regards to SQL Server, but not Oracle. 

So, eventually I figured out I needed to pass SSRS a SYS_REFCURSOR and then figured out this simple little SP.

Anyway, if this saves someone 20 minutes of googling, I thought I'd post it.  There is nothing revolutionary about it.

CREATE OR REPLACE PROCEDURE SCHEMA_NAME.USP_RPT_SP_NAME (PARAM1 IN DATE, PARAM2 IN DATE, p_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_recordset1 FOR
        SELECT * from TABLE_NAME
        WHERE ADDDTTM between PARAM1 and PARAM2;
END USP_RPT_SP_NAME;
/

Comments

danamatson said:

How did you get it to execute in your report? I get an Internal.net framework data provider error 30.

# April 8, 2010 2:49 PM