Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hagen
Regular Visitor

Oracle not listed as connection type

Hello,

when creating a new data source the Oracle connection type is not listed. The ODAC (x64 version) is installed. The  oracle.dataaccess.dll is in the GAC (C:\Windows\Microsoft.NET\assembly\GAC_64\Oracle.DataAccess\v4.0_4.122.1.0__89b483f429c47342).

 

in C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config there is an entry 

---snip---

<configuration>
<configSections>
<section name="oracle.unmanageddataaccess.client" type="OracleInternal.Common.CustomSectionHandler, Oracle.DataAccess, Version=4.122.0.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

...

<system.data>
<DbProviderFactories>
<add name="ODP.NET, Unmanaged Driver" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET, Unmanaged Driver" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=4.122.0.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

...

---snip---

and similar in C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG.

 

Anyway I cannot see the oracle connection type listed. In the Reporting Server log trace I can find:

---snip---

library!ReportServer_0-1!1694!08/14/2019-19:49:50:: i INFO: Call to GetSystemPropertiesAction(). User: ENZO-V1\Administrator.
extensionfactory!ReportServer_0-1!1b54!08/14/2019-19:49:50:: e ERROR: Exception caught instantiating ORACLE report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.
at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
at Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapper.CreateOracleConnection()

---snip---

Somehow I have the feeling it is because of the providerInvariantName. If I execute the following code snippet on the report server:

 

var mcs = DbProviderFactories.GetFactoryClasses();

foreach(DataRow mc in mcs.Rows)
{
Console.WriteLine(mc[0]);

}

 

I get this output:

Odbc Data Provider
OleDb Data Provider
OracleClient Data Provider
SqlClient Data Provider

So, the oracle data provider is not listed. I added the entries from machine.config (see above) to C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config and C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config (before it was in Framework64). Then the output was 

Odbc Data Provider
OleDb Data Provider
OracleClient Data Provider
SqlClient Data Provider
ODP.NET, Unmanaged Driver

However, after restarting the service the Oracle connection type is still not listed and I get the same error in the log (Unable to find the requested .Net Framework Data Provider).

Thank you for help.

Hagen

3 REPLIES 3
LuisGRG
Regular Visitor

Hi, in my case, i have and old instalation and the connection is listed. 

In our dev team enviroment we prepare a new server with the lastst version and is not showing it; but used this workaorund:

Install Oracle Client and config tns

Config the OleDB conection like this :

LuisGRG_0-1618511202417.png

 

hagen
Regular Visitor

I've rewritten the test-tool:

static void Main(string[] args)
        {
            var mcs = DbProviderFactories.GetFactoryClasses();
            foreach (DataRow mc in mcs.Rows)
            {
                if (mc[2].ToString().ToLower() == "oracle.dataaccess.client")
                {
                    Console.WriteLine("Found:");
                    for (int j = 0; j < mcs.Columns.Count; j++)
                    {
                        Console.WriteLine(mc[j]);
                    }
                }
                Console.WriteLine("");
            }

            try
            {
                var x = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
                Console.WriteLine("DbProviderFactories.GetFactory(\"Oracle.DataAccess.Client\") works!");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
                Console.WriteLine("Stack: " + e.StackTrace);
                Console.WriteLine("");
                if (e.InnerException != null)
                {
                    Console.WriteLine("Inner Exception:" + e.InnerException.Message);
                    Console.WriteLine("Inner Trace:" + e.InnerException.StackTrace);
                    Console.WriteLine("");
                }
                Console.WriteLine("");
            }
        }

and compiled it for x64 target platform. Outpu was:

Found:
ODP.NET, Unmanaged Driver
Oracle Data Provider for .NET, Unmanaged Driver
Oracle.DataAccess.Client
Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=4.122.0.0, Culture=neutral, PublicKeyToken=89b483f429c47342

Error: Failed to find or load the registered .Net Framework Data Provider.
Stack:    at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
   at TestOracleProviderFactory.Program.Main(String[] args) in C:\Users\hagen\source\repos\ConsoleApp1\ConsoleApp1\Program.cs:line 27

I get the version 4.122.0.0 from the machine.config, whereas in the GAC I have the version 4.122.1.0. This is because I used the selecthome.bat that was delivererd together with the ODAC Setup from Oracle. The batch contains the wrong version strings! I have changed the machine.config accordingly to version 4.122.1.0. The tool's output is now:

Found:
ODP.NET, Unmanaged Driver
Oracle Data Provider for .NET, Unmanaged Driver
Oracle.DataAccess.Client
Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342

DbProviderFactories.GetFactory("Oracle.DataAccess.Client") works!

Wow, seems to work. Restarted the power BI report server: No success! Still no oracle connection type visible in the drop down list and in the log I still can seee the same error:

xtensionfactory!ReportServer_0-2!1ad0!08/15/2019-12:55:24:: e ERROR: Exception caught instantiating ORACLE report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.

Frustrating...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.