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)

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.

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
CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad (
        p_STUDENT_ADNAME IN varchar2,
	l_CURSOR OUT sys_refcursor
    -- going to declare variables that are to be returned in SSRS but not
    -- mentioned in the above OUT variables:
      p_STUDENT_REF number(8);
      p_STUDENT_DETAILS varchar2(255);
    -- putting a begin within otherwise I get warning errors (with an end!)

    -- initial query to get me the student ID based on the inputted parameter
      student_accounts.student_username = p_STUDENT_ADNAME;

    -- opening cursor for the select query that I want to display in SSRS
      SELECT INITCAP(b.p_title)
       || ' '
       || INITCAP(b.p_forenames)
       || ' '
       || UPPER(b.p_surname)
       || ' ('
       || INITCAP(To_char(b.p_dob, 'DD-MON-YYYY'))
       || ')' "p_STUDENT_DETAILS",
       NVL(a.student_id, b.person_id) "p_STUDENT_REF"
        student_accounts a
      INNER JOIN student_details b ON a.student_id = b.person_id
      WHERE a.student_id = p_STUDENT_REF;

    -- ending that second begin I had after my variable declaration

             RAISE_APPLICATION_ERROR (-20001, p_STUDENT_ADNAME || ':$:' || SQLERRM, TRUE);

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.
CREATE PUBLIC SYNONYM sp_get_studentdetails_from_ad FOR base_table_owner.sp_get_studentdetails_from_ad;
GRANT EXECUTE ON sp_get_studentdetails_from_ad TO my_ssrs_user;
  • 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.

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!

Add comment



+1 Riyaz Thursday, 15th September 2016, 2:20 pm
Trying to call the below procedure from SSRS but keep getting error when hitting the Refresh Fields button
error: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PR_MYPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

create or replace PROCEDURE pr_myproc(pCURS OR out SYS_REFCURSOR)
0 kiquenet Tuesday, 24th November 2015, 9:31 pm
SSRS issue with Oracle: Cannot derive parameters for overloaded stored procedure or function in SSRS RDL execution

social.msdn.microsoft.com/.../ ...

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error de ejecución de consulta para el conjunto de datos 'TablaRamosVent as'. ---> System.InvalidO perationExcepti on: No se puede derivar parámetros para un procedimiento o función de almacenamiento sobrecargado.
en System.Data.Ora cleClient.Oracl eCommandBuilder .DeriveParamete rsFromStoredPro cedure(OracleCo nnection connection, OracleCommand command)
+1 Fabricio Thursday, 18th June 2015, 12:46 pm
Didn't worked for me. I'm still receiving the ORA-06550 error.

My procedure works fine in SQL Developer, but on SSRS designer I'm not even able to refresh the fields.
0 shady Monday, 26th January 2015, 6:34 pm
Will it work if i select multiple student names?
0 asde Tuesday, 4th June 2013, 12:08 am
thanks alot realy help me, it is very usefull
0 David S Wednesday, 6th February 2013, 9:47 pm
create or replace
PROCEDURE sp_RunMissingCh ecks (
l_CURSOR OUT sys_refcursor
DECLARE RECNT number(8);
--Divide each section into begin-end blocks
--Since my procedure has an output of the record count
--a variable needs to be placed.
-- 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.
Count(AGENT_NUMBER) as RecordCnt
--Simple once you get it done but the learning curve is nasty.
END sp_RunMissingCh ecks;
0 David S Tuesday, 5th February 2013, 10:24 pm
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:
-2 Joel L Wednesday, 6th February 2013, 11:28 am
Hi David,

I would think the most basic query would be to open a cursor. The following in theory should just return a table:
l_CURSOR OUT sys_refcursor

-- opening cursor for the select query that I want to display in SSRS
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.
0 TonyCovarrubias Tuesday, 1st September 2015, 5:14 pm

for this you don't need to call a procedure. Just run the query as your dataset in SSRS
0 Lukas S Wednesday, 26th September 2012, 9:24 am

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