Telerik Reports and Oracle.ManagedDataAccess.Client + Issues with MSDAORA

[Chuck Lorence]

Two weeks ago (on 12/15/2015) we upgraded from 9.1.15.731 to the 9.2.15.1126 version of Telerik Reports. As soon as we did, we ran into problems connecting to our Oracle 11g R2 database's stored procedures. We are using the Oracle.ManagedDataAccess.Client provider to connect our .Net 4.5.2 web application to the Oracle database. We found this odd because the 9.1.15.731 Telerik Reports version worked with said client. Here is the error we received while running the report and/or trying to edit the reports using Visual Studio 2015 with the Oracle.ManagedDataAccess.Client provider (I’ve removed the name of the report and stored proc name).

An error has occurred while processing Report '{NAME OF REPORT}':
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to '{STORED PROCEDURE NAME}'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

After much hair pulling and research we discovered that the 9.2.15.1126 reports would run using the MSDAORA provider. So we modified the web.config to us the MSDAORA provider when running a Telerik. This worked great on our local machines, but once we deployed the new code to our development server we began receiving the following error:

An error has occurred while processing Report 'Safety and Health Inspections and HAPs Status':
The 'MSDAORA' provider is not registered on the local machine.

We attempted to register the MSDAORA dll on the server using administrative privileges, but this results in a “failed to load” error. 

So now we’re stuck. Has anyone had any issues similar to ours getting the 9.2.15.1126 version of reports to run using Oracle.ManagedDataAccess.Client OR has anyone successfully deployed said version using MSDAORA?

[Stef]

Hello Chuck,

Below are quotes from a discussion with your colleague on the same question:
" The SqlDataSource wizard relies on the selected .NET data provider to evaluate the SQL query and to provide information about any detected SQL parameters. The syntax of the SQL query must be considered with the selected .NET data provider in order SQL parameters to be listed at the 'Configure data source parameters' step of the SqlDataSource Wizard.
In case the .NET data provider does not return information about SQL parameters, you can configure manually the SqlDataSource component's Parameters collection.

For Oracle database, if parameters are specified via "?", the order of parameters is important for how the SqlDataSource.Parameters will be passed to the SQL query.


About the reported error "The 'MSDAORA' provider is not registered on the local machine.", please check the following Oracle forum thread: 'MSDAORA' provider is not registered on local machine.
.........

MSADORA provider has only 32bit version and can be used by an application with x86 platform target (build settings). The provider must be installed and registered on the machine, where you can test with the Standalone Designer (Telerik.ReportDesigner.x86.exe) if the provider is properly configured (if it is, the designer will load it in the SqlDataSource wizard).

I can suggest you to test also Oracle.DataAccess.Client - 64-bit Oracle Data Access Components (ODAC) Downloads, where SQL queries syntax can be verified by executing a test query in code with the same .NET data provider and the associated ADO.NET classes. "

The recommended Telerik Reporting version is Q3 2015 SP2 v9.2.15.1216 (an internal build).


Please let us continue the discussion in one of both threads in order to keep a better track on the exchanged information.
Thank you for your understanding.

Regards,
Stef
Telerik

[Chuck Lorence]

Thank you for your assistance. After a good bit of time on Google we finally discovered the missing piece to the puzzle. In the end we were able to use the OraOLEDB.Oracle provider. Apparently the OraOLEDB.Oracle provider requires that you specify when the report is using a PL/SQL stored procedure to return a rowset. This is done by including “PLSQLRSet = 1” in the connection string. In the end the following is the web.config entry we used for the Telerik reports. We use a separate Visual Studio project for our Telerk Reports, therefore this entry was also required in the app.config within that particular VS project (this allows you to see data in the report preview).

<connectionStrings>

<add name="TelerikReports" connectionString="Provider=OraOLEDB.Oracle;Password={password};User ID={userID};Data Source={TNSNames Entry};PLSQLRSet=1;" providerName="System.Data.OleDb" />

</connectionStrings>

We found the following oracle documentation helpful:
https://docs.oracle.com/cd/A97630_01/win.920/a95498/using.htm

I'm hoping that in the future the Telerik reports will work with Oracle's recommended provider - Oracle.ManagedDataAccess.Client 

 

[Stef]

Hello Chuck,

In general, the SqlDataSource wizard uses standard ADO.NET classes associated with the selected .NEt data provider to retrieve data. If the data can be retrieved in code in a DataSet, the SqlDataSource would use the same logic. Issues that may occur are related to how different .NET data providers implement reading and passing values to parameters, and executing queries.

Please post the SqlDataSource settings and the exception's stack trace when you use the Oracle.ManagedDataAccess.Client provider. Include also details about the used SQL query.

Thank you for your cooperation.

Regards,
Stef
Telerik