Hi,

Our SSRS reports have been working for years. They retrieve data from stored procedures on an Oracle 11 DB using the Oracle 11 Client. After the Oracle Client on the SSRS server was upgraded from version 11 to 12, none of the reports work anymore.

Trying to open any report in IE displays the following error:

Query execution failed for dataset 'ds_MyDataset'. ---> System.Data.OracleClient.OracleException:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'usp_MyOracleStoredProc'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The simplest example we're using to troubleshoot the problem is a report that has no parameters, which calls an Oracle stored procedure which has no input parameters, and only 1 output parameter (of type "Ref Cursor", the one Oracle needs in order to return the resultset). Even this simple report produces the error shown above.

I placed a "debugging aid" inside the body of the stored procedure, a SQL insert statement to log to a table, so I can see if the body of the procedure's being executed at all. It never runs. That leads me to think that the error occurs before executing the procedure, when SSRS is probably using something like DeriveParameters() to detect the number and type of parameters the procedure has.

This leads me to think that when SSRS detects which parameters the Oracle procedure has, the Oracle Client returns 1 parameter (the output cursor). SSRS looks at the number of parameters defined in the report's dataset, and it finds zero b/c the output cursor parameter Oracle uses to return the data doesn't need or can't be defined in SSRS. Then SSRS compares 1 <> 0, and displays the error.

So, in order to test, I removed the output parameter (the one of type "Ref Cursor") from the Oracle stored procedure, and then the report runs, and the body of the procedure gets executed. There are no errors, but no data b/c the output cursor parameter has being removed from the Oracle procedure.

This leads me to think that in this case, when SSRS detects which parameters the Oracle procedure has, the Oracle Client returns zero parameters. SSRS looks at the number of parameters defined in the report's dataset, and it matches b/c there aren't any, so SSRS proceeds to execute the procedure.

Could there be a "miscommunication" between SSRS 2208 R2 and Oracle Client 12 in reference to the number of parameters, b/c with Client 11 all reports work, but with 12 none works?

Any help with this matter will be greatly appreciated.

Sincerely,

Richard

SSRS 2008 R2 calls Oracle stor proc produces error invalid number or type of parameters

Hi Richard,

Thank you for your question.
 
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Best regards,
Qiuyun Yu

Hi Richard,

Thank you for your question.
 
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Best regards,
Qiuyun Yu