Monday, October 20, 2014
   
Text Size
Login

Returning Oracle Stored Procedure Resultset in SSRS

What?
So I've spent a fun time googling and binging but still haven't found a simple and complete example of getting a resultset from an Oracle stored procedure and displaying this in SQL Server Reporting Services (SSRS). Well "non-productive" more than "fun" as most of the examples on the net are either half-complete or partially documented. So here goes...
  • Using Business Intelligence Development Studio v2008 (BIDS)
  • SQL Server Reporting Services v2008 R2 (SSRS)
  • Oracle SQL Developer v3 (you can use any equivalent, eg. SQL*Plus)

Why?
I think this is one of those very rare occasions that Microsoft people can say "it's so much easier using a Microsoft product to work with another Microsoft product" (ie "Seamless integration"). Yes, I'm trying to get an SSRS report to display the results from an Oracle stored procedure. I have a previous article describing a basic stored procedure in Oracle, this article aims to outline how to apply this to an SSRS 2008 R2 report.

How?
If you've googled this topic as much as I have, you'll note that everyone seems to be recommending using a cursor. I went through about 10 pages of google results without working out how to use these in SSRS and then resorted to my old methods of debugging. For those of you who don't know what a cursor is, you can find this info on another site as this article focuses on getting an Oracle stored procedure to work with SSRS.

Main Example:
My stored procedure will do the following:
  1. Get a student ID number based on the student username
  2. Get a student's names and date-of-birth based on the student ID number
Agenda Outline
Note: As simple as this sounds, this is not a five-minute job. Even with all your SQL scripts with backups of your datasets, each of these will need tweaking.
  1. Create your stored procedure in Oracle (and grant execute for your SSRS user)
  2. Add a data source to your SSRS report
  3. Add an SSRS input parameter
  4. Add a dataset
    1. Query type: Stored Procedure
    2. Refresh fields button
    3. Parameter name (oracle input sp variable) and Parameter value (ssrs input parameter)
    4. Fields (field name=ssrs reference, field source=oracle sp field name)

1. Create a stored procedure using a cursor for SSRS

  1.  CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad ( 
  2.          p_STUDENT_ADNAME IN varchar2, 
  3.       l_CURSOR OUT sys_refcursor 
  4.  ) 
  5.  IS 
  6.  BEGIN 
  7.      -- going to declare variables that are to be returned in SSRS but not 
  8.      -- mentioned in the above OUT variables: 
  9.      DECLARE 
  10.        p_STUDENT_REF number(8); 
  11.        p_STUDENT_DETAILS varchar2(255); 
  12.       
  13.      -- putting a begin within otherwise I get warning errors (with an end!) 
  14.      BEGIN 
  15.   
  16.      -- initial query to get me the student ID based on the inputted parameter 
  17.      SELECT 
  18.        student_accounts.student_id 
  19.      INTO 
  20.        p_STUDENT_REF 
  21.      FROM 
  22.        student_accounts 
  23.      WHERE 
  24.        student_accounts.student_username = p_STUDENT_ADNAME; 
  25.   
  26.      -- opening cursor for the select query that I want to display in SSRS 
  27.      OPEN l_CURSOR FOR 
  28.        SELECT INITCAP(b.p_title) 
  29.         || ' ' 
  30.         || INITCAP(b.p_forenames) 
  31.         || ' ' 
  32.         || UPPER(b.p_surname) 
  33.         || ' (' 
  34.         || INITCAP(To_char(b.p_dob, 'DD-MON-YYYY')) 
  35.         || ')' "p_STUDENT_DETAILS", 
  36.         NVL(a.student_id, b.person_id) "p_STUDENT_REF" 
  37.        FROM 
  38.          student_accounts a 
  39.        INNER JOIN student_details b ON a.student_id = b.person_id 
  40.        WHERE a.student_id = p_STUDENT_REF; 
  41.   
  42.      -- ending that second begin I had after my variable declaration 
  43.      END; 
  44.   
  45.      EXCEPTION 
  46.          WHEN OTHERS THEN 
  47.               RAISE_APPLICATION_ERROR (-20001, p_STUDENT_ADNAME || ':$:' || SQLERRM, TRUE); 
  48.   
  49.  END sp_get_studentdetails_from_ad; 
Permissions on this SP
Might need to give permission for your SSRS user (specified credentials in data source) to run this stored procedure.

  1.  CREATE PUBLIC SYNONYM sp_get_studentdetails_from_ad FOR base_table_owner.sp_get_studentdetails_from_ad; 
  2.  GRANT EXECUTE ON sp_get_studentdetails_from_ad TO my_ssrs_user; 
Notes:
  • No "SELECT ... INTO ..." on the second query which is the one that will return my columns.
  • Two output fields are expected to be returned: "p_STUDENT_DETAILS" and "p_STUDENT_REF".

2. Add a datasource
Nothing different to your usual method of connecting to a data source, I just added this section to say that my data source is type "Oracle" and NOT ODBC or OLEDB. Unicode=true and my user is a specific read-only user.

3. Add an SSRS parameter
Idem as in just specify a parameter as you would for any usual SSRS report. (Ignore my 2nd and 3rd SSRS parameters in my example as I didn't end up using them)

4. Add a dataset
Follow these steps carefully. I've added some screenshot images to help and highlighted areas which you should ensure are correct (or which applies to your report)

4a. Add a dataset: query type "Stored Procedure"
Click on the radio button when adding your dataset and then select the stored procedure from the dropdown list.

4b. Add a dataset: the "Refresh fields..." button
Click on the "Refresh fields..." button so that you can define the query parameters.

4c. Add a dataset: the "Parameter Names"
Under "Parameter Name" type the exact same name for the input variable used in your Oracle stored procedure (in my example: p_STUDENT_ADNAME). Under "Parameter Value" type the exact same name of your SSRS parameter prefixed with a square bracket and the at sign and then suffixed with a closing square bracket (in my example: [@StudentReference]).

Adding a dataset for an Oracle stored procedure
4d. Add a dataset: returned "Fields"
These is where you "map" the variables to the oracle returned variables.
Adding a dataset for an Oracle stored procedure

Debug Testing
"Process of elimination" is possibly my favorite and most reliable way of figuring out what's wrong. Here are some suggestions:
  • Check your Oracle stored procedure compiles without warnings in SQL Developer (or SQL*Plus, etc.)
  • Hardcode your parameters in the SSRS report with actual values that would have been submitted.
  • Create the most basic report you can with the data you're working with just so you can get used to how this works.
  • IMPORTANT! Make sure you have prefixed the parameter name with an at sign and surrounded the input parameter between square parameters, example: [@StudentReference]. Fixing this resolved all 3 issues below!!!
Encountered Issues
  • ORA-01403 no data found & ORA-06512 something else - Fixed by specifying the query parameter properly (ex: Parameter name = p_STUDENT_ADNAME, Parameter Value = [@StudentReference] - NOTE THE SQUARE BRACKETS FOR PARAMETER VALUE)
  • PLS-00306: Wrong number or types of arguments in call - Fixed this by double-checking the parameters. Input variable has to be listed in the "dataset properties" > "query parameter values" under "Parameter Name" and then the SSRS parameter value (eg. "[@my_ssrs_parameter]").
  • Internal .Net Framework Data Provider error 30 - Wrong datatypes I was told but found this is just how I was calling the parameter. I declared these in the stored procedure but the fix in the end was referring to the Query Parameter Value properly.
  • IMPORTANT! Make sure you have prefixed the parameter name with an at sign and surrounded the input parameter between square parameters, example: [@StudentReference]. I cannot stress enough how much delay this minor detail caused!!!
  • No data displays in SSRS but procedure compiled without warnings - My fault really, the field name aliases specified in my second select query (the one preceded with "open cursor for") didn't match my SSRS report field name mapping.

Done!
Well almost as you still need to design your report. At least this article should have got you to the part where you're simply specifying what data displays where. It took me 4 days to figure this out then document it. Here's to hoping that this saves you some time!

Comments   

Lukas Strilek
# Lukas Wed, 26th September 2012
Hello,

great thanks to the author. I have found the article after some hours of internet searching. I have lived through similar troubles as the author describes at the beginnig. This has hepled to me very much.

BR, Lukas
Like | Dislike | 0 Reply | Reply with quote | Quote
David Starr
# David Tue, 5th February 2013
Okay, I keep finding this type of answer on how to run an oracle sp from SSRS all over the net. Yours is the best so far as detail and clarity goes. What I need is a bit more simple. How to kick off a sp with out parameters and with little or no output, single field at worst. The only output is a record count from the sp. This has stumped our Oracle guru and the SQL Server gurus are being real slow in getting back to me on it. :sad:
Like | Dislike | 0 Reply | Reply with quote | Quote
# Webmaster Wed, 6th February 2013
Hi David,

I would think the most basic query would be to open a cursor. The following in theory should just return a table:
Code:
CREATE OR REPLACE PROCEDURE sp_getsomedata (
l_CURSOR OUT sys_refcursor
)
IS
BEGIN

-- opening cursor for the select query that I want to display in SSRS
OPEN l_CURSOR FOR
SELECT
a.my_column_1,
a.my_column_2,
a.my_column_3
FROM
my_table a;

END sp_getsomedata;


Your report should return the columns "my_column_1, my_column_2, and my_column_3" See picture above on point 4d.
Like | Dislike | 0 Reply | Reply with quote | Quote
David Starr
# David Wed, 6th February 2013
create or replace
PROCEDURE sp_RunMissingCh ecks (
l_CURSOR OUT sys_refcursor
)
IS
BEGIN
DECLARE RECNT number(8);
--Divide each section into begin-end blocks
Begin
--Since my procedure has an output of the record count
--a variable needs to be placed.
AGCK_MISSING_CHECKS(RECNT);
End;
-- opening cursor for the select query to get the output
-- that the SSRS DI is looking for.
-- In this case a record count from the refreshed table.
Begin
OPEN l_CURSOR FOR
SELECT
Count(AGENT_NUMBER) as RecordCnt
FROM
AGCK_MISSING;
End;
--Simple once you get it done but the learning curve is nasty.
END sp_RunMissingCh ecks;
Like | Dislike | 0 Reply | Reply with quote | Quote
asde
# asde Tue, 4th June 2013
thanks alot realy help me, it is very usefull
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Convert to Proper Case in T-SQL

    • Tue 07-Oct-14
      Really comprehensive function. Nice work! Dave.
      starsky51
  • Joes Quicklist Weblinks (JQW)

    • Wed 08-Oct-14
      Thanks jazzmang! This module is due for an update so I'll review this issue in the next version.
      Webmaster  
    • Tue 07-Oct-14
      I've set "Display Images" under Module Settings but the modules continues to display a hard coded CSS ...
      jazzmang
  • Joes Word Cloud (JWC)

    • Thu 16-Oct-14
      hello i use your tag cloud component in my joomla 2.5 website. i'm french and there is probleme with ...
      plykite2010  
    • Tue 07-Oct-14
      Hello, i have Joomla 2.5.27 and Module JWC v3.2.2 installed. In Configuration i fill in how many Word ...
      Maic