Last Updated on Monday, 30 January 2012
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.
My stored procedure will do the following:
- Get a student ID number based on the student username
- Get a student's names and date-of-birth based on the student ID number
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.
- Create your stored procedure in Oracle (and grant execute for your SSRS user)
- Add a data source to your SSRS report
- Add an SSRS input parameter
- Add a dataset
- Query type: Stored Procedure
- Refresh fields button
- Parameter name (oracle input sp variable) and Parameter value (ssrs input parameter)
- Fields (field name=ssrs reference, field source=oracle sp field name)
1. Create a stored procedure using a cursor for SSRS
Permissions on this SP
- 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
- OPEN l_CURSOR FOR
- 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
- WHEN OTHERS THEN
- RAISE_APPLICATION_ERROR (-20001, p_STUDENT_ADNAME || ':$:' || SQLERRM, TRUE);
- END sp_get_studentdetails_from_ad;
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]).
These is where you "map" the variables to the oracle returned variables.
"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!!!
- 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!