SSRS Parameters in Oracle Stored Procedure

I'm storing a note here because it took a while to figure out and googling other solutions did not answer our questions but did lead us to a workaround.

So this is regarding an error when trying to add parameters to a Stored Procedure of an Oracle database from within Microsoft SQL Server Reporting Services.

The Error
copyraw
ORA-00911: invalid character 
ORA-06512: at "SYS.DBMS_UTILITY", line 114 
ORA-06512: at line 1 (System.Data.OracleClient)
  1.  ORA-00911: invalid character 
  2.  ORA-06512: at "SYS.DBMS_UTILITY", line 114 
  3.  ORA-06512: at line 1 (System.Data.OracleClient) 

The Situation
I'm using Business Intelligence Development Studio 2008 to develop a Reporting solution on a Windows XP workstation. We are connecting to a SQL Server 2008 R2 Reporting Service (SSRS) with Team Foundation Server 2010. The database with our stored procedure is Oracle 10g.

The Stored Procedure
Rather than a returned dataset, our Stored Procedure updates a field in one of our Oracle tables and returns nothing. It accepts 3 parameters: The StudentID (reference) varchar2, Username (who's running the report) varchar2 and a JobID (request reference) number.

The Report
We start off with our detailed report page which lists all the details of a student. There is a textbox linked to an action which will open another report (the confirmation page) and pass through 2 parameters (the Student ID and the Job ID [for audit purposes]). The 3rd parameter is the Username of the person running the report. This second page is called "HouseKeepForm". We have a 3rd report which will actually execute the stored procedure called "HouseKeepExec".

The Problem
We get the above error if we tried to execute the stored procedure within the dataset query. Initially our dataset query was:
copyraw
EXEC MY_STORED_PROCEDURE(:Parameter1, :Parameter2, :Parameter3);
  1.  EXEC MY_STORED_PROCEDURE(:Parameter1, :Parameter2, :Parameter3)


The Solution
To setup the call to the stored procedure for Oracle we had to do the following:
  1. We have our main report with a textbox that has the Action property: Go to Report / HouseKeepForm (our confirmation page) / With 2 parameters provided in the main report (note: main report runs with incident number as NULL, the user can then add an incident number afterwards or when the initial report was run).
    Text Box Properties: Action: Go to Report
  2. The confirmation page just checks with the end-user that this is what they really want to do as we don't really have an immediate undo process. The form page carries the cancel button (textbox which actions to the previous report with same parameters) and another link which leads to our report containing the call to the Stored Procedure. This page itself contains a basic dataset query just to check with the end-user:
    Text Box Properties: Action: Go to Report

    Note that this an ordinary report as per usual. It will act as a confirmation page (already holds the parameter passed to it from the previous page and that it will pass to the next page which executes the stored procedure)
    alt
  3. So this is the 3rd page which will actually execute our stored procedure. Here we have called the dataset to launch the Stored Procedure as "SP". You can specify command type and stored procedure name here or you can do this in the next screenshot. Note how all our variables are hidden and cannot be NULL.
    alt
  4. If you're unsure, right-click on your dataset and bring up its properties...
    alt
  5. Specify the query type as "Stored Procedure" and then browse the dropdown for the name of your stored procedure (should be listed if datasource is correct):
    alt
  6. Before you close this box, click on "Parameters" in the right and specify your parameters in the order that they are accepted by your stored procedure (eg. MyStoredProcedure(param1,param2,param3) -> param1=@param1, param2=@param2, param3=@param3):
    alt

Local Variables
Note that we were trying to execute our stored procedure with the parameters specified just after the name of the procedure. The trick is not to use any of these and to use the GUI dialog of the "Dataset Properties" for that stored procedure. The variable values will be prefixed with @ for SQL Server and not the colon (:) we expected for Oracle.

Passing in Variables
I've been told that SSRS does not understand OUT variables. For a success message to be returned based on the status of the stored procedure, please view my article SSRS Retrieving Oracle Stored Procedure Success or ErrorLevel
copyraw
EXECUTE MY_PRETEND_STORED_PROCEDURE('value1','value2','value3',:errorcode,:errormsg)
  1.  EXECUTE MY_PRETEND_STORED_PROCEDURE('value1','value2','value3',:errorcode,:errormsg) 
Still discussing this stage... Not proceeding with this as there may be a better way. In addition, SSRS was complaining about assignment targets not being valid (despite knowing what they are).

Searches that got me nowhere
Well not entirely true, there were solutions and tidbits but everyone had a different setup to us and their solutions were complex coding solutions and ultimately the wrong route as we worked out our own solution.
  • ssrs run oracle stored procedure
  • execute call oracle stored procedure in ssrs
  • ssrs oracle stored procedure and parameters
  • ssrs execute stored procedure raise exception
Category: SQL Server Reporting Services :: Article: 380

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 bc1qjtp4l4ra452wzvuk9a45yfj82zkahsyy2z379y
© 2022 Joel Lipman .com. All Rights Reserved.