Unable to call a Oracle stored procedure from SSRS report builder 3.0

0
votes

This problem has been posted by many developers but unfortunately I couldn't find any solution that I can use.

We are moving from Crystal Reports to SSRS reports (Report Builder 3.0), for the report development we are using oracle driver and stored procedure as the connection option.

When I try to run the query or any table I was able to run and get the required data set but when stored procedure (2 input 1 output sys_refcursor) is used I am getting below error:

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

I tried different ways but couldn't proceed a single step ahead.

1
oraclestored-procedurescrystal-reportsssrs-2008-r2reportbuilder3.0

1 Answers

0
votes

Finally after trying for many days, found a way to use stored procedure in SSRS.

posting the answer so that others can use.

From report builder I couldn't find any solution to connect a stored procedure to oracle database, Followed below process:

  1. Install visual studio 2017

  2. Install Microsoft.DataTools.ReportingServices for visual studio this will install the SSRS tools to develop from visual studio.

  3. Now to get the oracle connection need to install the ODAC tools. For the connection to work perfectly install ODAC tools for 32 bit (Select all options during installation) and ODAC tools for 64 bit with out "Oracle developer tools for visual studio".

Now when you start visual studio, make connection to report and the select the procedure it should automatically give you the meta data for the procedure.