SSRS Parameters in Oracle Stored Procedure

: Joel Lipman; SQL Server Reporting Services; 02 September 2011; Hits: 16683

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

copy raw

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

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
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:

copy raw

EXEC MY_STORED_PROCEDURE(:Parameter1, :Parameter2, :Parameter3);

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:

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).
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:

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)
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.
If you're unsure, right-click on your dataset and bring up its properties...
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):
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):

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

copy raw

EXECUTE MY_PRETEND_STORED_PROCEDURE('value1','value2','value3',:errorcode,:errormsg)

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.