SSRS Report not able to call Oracle Stored Procedure having OUT parameter

Question

Error: PLS-00306: wrong number or types of arguments in call to 'TEST_ORACLE_SP_WITH_OUT_REF_CURSOR' Please note the following points: 1. No issues when running the above SP from the SSRS Report Designer itself. But only when the same report is called from SSRS Report Server, the above error is encountered. 2. We are using Oracle 12.2 and the corresponding ODAC 32-bit and 64-bit drivers are installed in the Server. But not Visual Studio 2015. 3. The developer's machine where Report Designer is installed, Visual Studio 2015 also is installed and the report works fine there. It has been observed that with installation of Visual Studio 2015 in the Report Server machine, the issue gets resolved. But it's not possible to install Visual Studio in a Server machine. It's in fact prohibited and no one does that. 4. The SP has just one IN parameter and one OUT parameter which is of type SYS_REFCURSOR. Please let me know what could be the issue behind this strange behavior and what is the remedy.

Answer 1

Hi @AmlanG ,

Error: PLS-00306: wrong number or types of arguments in call to 'TEST_ORACLE_SP_WITH_OUT_REF_CURSOR'

This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.

Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

Add the connection option ProcedureRetResults=1 to the Connect for ODBC Oracle Wire Protocol driver connection string in order to tell the ODBC driver that a resultset can be returned by a stored procedure.

Alternatively update the ODBC data source to indicate that a resultset could be returned by a stored procedure:

• On Windows, configure the ODBC data source and tick the option 'Procedure returns Results' on the advanced tab.
• On Unix, set ProcedureRetResults=1 in the data source section.

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.