SSRS Report fails when trying to run Oracle Stored Procedure

Question

I have an issue whereby I can execute an SSRS report which calls an Oracle Stored Procedure locally in VS2017, but when I deploy to the SSRS Server and run, it returns the following message:-

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'spTestSubDet'. (rsErrorExecutingCommand)
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SPTESTSUBDET' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

The dataset 'spTestSubDet' is the Oracle Stored Proc.

Stored Proc SQL code:-

create or replace

PROCEDURE SPTESTSUBDET

(s1 OUT SYS_REFCURSOR)

IS

BEGIN

 OPEN s1 FOR

 SELECT

 *

FROM

 onemain.sbceysubmitted sbceysub

WHERE

 sbceysub.STUD_ID = 167071

 ;

END SPTESTSUBDET;

It’s as simple a test as I can put together and doesn’t use any parameters to complicate things.

Some configuration details:-

VS2017 version 15.9.24.

MS .NET Framework v 4.8.03761.

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 – Production.

SSRS version 15.0.19528.0.

SQL Server version 2014.

ODAC v18.3.0.

SQL Developer Version 3.2.20.10

I can execute SQL code and Views against the Oracle server with the same DSN from the deployed report (having removed the oracle stored proc), so I believe the DSN configuration is not the issue.

SQL code and Views present no problems from other deployed SSRS reports.

I have also check marked the "Use single transaction when processing the queries" box in the SSRS DS Properties as I understand “Oracle's stored procedures don't return recordsets”.

I’m guessing that it might be some form of “Execute” permissions issue on Oracle, rather than the Report Server, where the Stored Proc is concerned.

However, I also wonder if it’s a driver issue, though in both cases, it puzzles me that the report will run locally without any problem.

As a developer, I don’t have any DBA permissions to interrogate how the SSRS Report Server or the Oracle DB, is set up, so any suggestions on that front will have to be passed on to my ICT dept.

Any thoughts/suggestions would be greatly appreciated.

Answer 1

Based upon the error it is a parameter problem. Unlike SQL, Oracle requires that you pass all parameters to a sproc (assuming it is a sproc, Oracle sprocs are not SQL sprocs). The sproc you're calling requires an output cursor to be passed. Are you doing that?

Returning a cursor from a sproc seems odd to me so I'm not sure how you'd code this up in SSRS. Can you show us how you're calling this in SSRS? I can see a post from back in 2012 that says SSRS will use the old OracleClient class to talk to Oracle (at least then) and that cursors had to be treated specially. Personally I'd switch the sproc to return a simple type and verify it is working correctly. If it is then move on to the cursor as I suspect this is the issue.

Answer 2

Hi @NJC59 ,
I think this error seems to indicate some permission issue.
When I was studying the error "rsErrorExecutingCommand", I found this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/72a4e01e-89b7-4353-b375-63646b266006/rserrorexecutingcommand?forum=sqlreportingservices .
When you describe that reports can be queried locally but cannot be deployed, what I can think of is whether you have the permission to deploy reports to the report manager. Because the 2017 version of VS has the problem of not being able to upload a single report, could you try to deploy a simple report?
To be honest, it is impossible to view your server environment in the forum, so it is more difficult to resolve this type of issue. I suggest you consider choosing the telephone support service provided by Microsoft (you need to pay some fees), but the issue can usually be resolved.
https://support.microsoft.com/en-us/help/4051701/global-customer-service-phone-numbers
https://support.microsoft.com/en-us/supportforbusiness/productselection
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.