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
(s1 OUT SYS_REFCURSOR)
OPEN s1 FOR
sbceysub.STUD_ID = 167071
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 220.127.116.11.0 – Production.
SSRS version 15.0.19528.0.
SQL Server version 2014.
SQL Developer Version 18.104.22.168
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.