In the prerequisites section of the Qlik Compose user guide, the instructions state that it is required to add the path/directory to Oracle.DataAccess.dll in machine.config and to also register the .dll in GAC.
In addition to the Oracle Documentation, this article aims to help users set up their Oracle Database as a source when using Qlik Compose.
Prerequisites
- Download ODAC 19c from Oracle: https://www.oracle.com/database/technologies/net-downloads.html
- Unzip the folder you´ve downloaded.
- Open CMD as Administrator and navigate to the folder you've just unzipped.
- RUN:
install.bat all c:\oracle [$oracle_home] true
where [$oracle_home] is the location of the Oracle home directory on the machine.
Steps
- Change the directory to c:\oracle\odp.net\bin\4> (or the directory where you have installed ODAC - this directory was specified during the install.bat step).
RUN:
OraProvCfg.exe /action:gac /providerpath:Oracle.DataAccess.dll
No need to specify the whole path since OraProvCfg and the .dll are in the same folder.
Sample Output:
c:\oracle\odp.net\bin\4>OraProvCfg /action:gac /providerpath:Oracle.DataAccess.dll - this will register the ODP.NET UNMANAGED DRIVER
INFO: oracle.dataaccess.dll is registered successfully in GAC.
- From the same directory Product is "/product:odp" for Unmanaged driver
RUN:
OraProvCfg.exe /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll
Sample Output:
c:\oracle\odp.net\bin\4>OraProvCfg.exe /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll
INFO: The following element added under 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.19.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
- Change directory to: C:\oracle\odp.net\managed\x64 and run "configure.bat". - This will add the keys to Windows Registry.
Sample output:
C:\oracle\odp.net\managed\x64>configure.bat
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle Data Provider for .NET, Managed Driver" /v EventMessageFile /t REG_EXPAND_SZ /d C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\EventLogMessages.dll /f
The operation completed successfully.
reg query HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319\AssemblyFoldersEx\odp.net.managed\
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319\AssemblyFoldersEx\Oracle.ManagedDataAccess" /ve /t REG_SZ /d "C:\oracle\odp.net\managed"\common /f
The operation completed successfully.
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319\AssemblyFoldersEx\Oracle.ManagedDataAccess.EntityFramework6" /ve /t REG_SZ /d "C:\oracle\odp.net\managed"\common\EF6 /f
The operation completed successfully.
- Then register the ODP MANAGED DRIVER in GAC add the .dll to machine.config: Here we have to specify the full path to the ManagedDataAccess.dll file since it is not located in the same folder as OraProvCfg.
The path is C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll which we'll be adding after /providerpath as shown below.
Within Directory of C:\oracle\odp.net\managed\x64
RUN:
OraProvCfg.exe /action:gac /providerpath:C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll
Sample output:
C:\oracle\odp.net\managed\x64>OraProvCfg.exe /action:gac /providerpath:C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll
INFO: c:\oracle\odp.net\managed\common\oracle.manageddataaccess.dll is registered successfully in GAC.
- Within Directory of C:\oracle\odp.net\managed\x64>
Note that product in this case is "/product:odpm"
RUN:
OraProvCfg.exe /action:config /force /product:odpm /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll
Sample output:
C:\oracle\odp.net\managed\x64>OraProvCfg.exe /action:config /force /product:odpm /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll
INFO: The following element added under DbProviderFactories.
<add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.19.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
Please restart Compose services after performing the above configuration and test the connection to the Oracle source.
Environment
Qlik Compose
Qlik Compose for Data Warehouses
Qlik Compose for Data Lakes
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
Related Content
Oracle ODAC Version 19c from: https://www.oracle.com/database/technologies/net-downloads.html#
Commands that add the .dll paths into machine.config and register in GAC. Please note the paths displayed are the default paths and may need to be edited to reflect the path on your system.
OraProvCfg.exe /action:gac /providerpath:Oracle.DataAccess.dll
OraProvCfg.exe /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll
OraProvCfg.exe /action:gac /providerpath:C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll
OraProvCfg.exe /action:config /force /product:odpm /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll
Command that registers the managed driver in Windows Registry (HKEY LOCAL MACHINE).
C:\oracle\odp.net\managed\x64>configure.bat
