Reporting Services calling an Oracle Stored Procedure

Most of the recent SSRS work I have done has been off a SQL Server datasource. And, for anything other than the simplest queries you'd write a Stored Procedure (SP) in the database which returned the rows you want. This is achieved nicely in T-SQL as you can do whatever you want in the body of the SP e.g set flags, create temporary tables, build objects and then end your SP by just writing a SELECT statement. The rows returned get picked up by the SSRS Report in the datasource and once you've mapped your fields then you are away.

In Oracle I didn't find it as simple. Maybe a combination of letting my PLSQL get rusty but also I think that PLSQL is just not geared up to do this in as simple a way.

In any case, I did get it working, and once you know how it is straightforward! The process is to create a Procedure in your Oracle database whose last parameter is and OUT parameter of type SYS_REF_CURSOR. Here is a short example of one I wrote:

 PROCEDURE spPickOrderStatus(pOrderStatuses OUT SYS_REFCURSOR) IS
  BEGIN


   OPEN pOrderStatuses
   FOR
   SELECT
      oid.order_ind, 
      order_ind_desc
   FROM
      mm_order_indicators_dim;


   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END;


Change the datasource in you report to be Oracle and choose the query type to be "Stored Procedure". The field should be mapped as smoothly as with a SQL Server datasource.