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

greenmtnsun — Mon, 27 Aug 2007 22:09:00 GMT

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;
/

greenmtnsun : Stored Procedure

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