- Oracle Client Software Version 8.1 7 Or Greater Free Download 64-bit
- Oracle Client Software Version 8.1 7 Or Greater Free Download 32-bit
This discussion is archived. 1 Reply Latest reply on Sep 15, 2008 9:18 PM by 247514. Oracle Client 8.1.7 required! Copy and paste the Oracle instant client drive in under the folder 'oracleodbc' in the following directory: C:oracleodbc. Check the content of the folder and make sure the oci.dll file is in the directory. SQLTools is a spin-off of the original SQLTools for Oracle. Like the original SQLTools SQLTools is a light weight and robust Windows GUI SQL frontend for Oracle databases. It works with Oracle 7.3/8/8i/9i/10g and 11g. Note: Oracle Client required.
To connect to an Oracle database with Power BI Desktop, the correct Oracle client software must be installed on the computer running Power BI Desktop. The Oracle client software you use depends on which version of Power BI Desktop you've installed: 32-bit or 64-bit. It also depends on your version of Oracle server. Supported Oracle versions.
In this article I will explain cause and resolution of “System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.” error
This error generally occurs when you deploy an application on the server that access Oracle database using “System.Data.OracleClient” library and it doesn’t find the library on the server.
To resolve this error you need to install Oracle Data Access Components (ODAC) with Xcopy deployment on the server which enables Oracle Data Provider for .NET (ODP.NET). Please follow these steps to install ODAC on the server
Step 1: Download ODAC component
For 32-bit download ODAC1120320Xcopy_32bit.zip from here
For 64-bit download ODAC112021Xcopy_x64.zip from here
You have to sign up to download these from oracle.com. I will download and install ODAC for 64-bit.
Step 2: Extract downloaded zip file anywhere on the system for example on the Desktop
Step 3: Open Command Prompt (cmd) and run following command to change directory to the extracted folder:
Step 4: Run following command in the Command Prompt to install Oracle Data Access Components
install.bat odp.net20 c:oracle11.2odac odac11
Step 5: Add following paths of the installed ODAC components in the Path environment variable of the system
Follow these steps to add paths to the environment variable
Step 5.1: Right click on “Computer” and select “Properties” to open the System properties
Step 5.2: Click on “Advanced system settings” to open “System Properties”
Step 5.3: Click on “Environment Variables” button under “Advanced” tab to open “Environment Variables” window
Step 5.4: Double click on the “Path” variable in the “System Variables” (scroll down if it’s not visible) section to edit the “Path” variable
Step 5.5: Add C:oracle11.2odac;C:oracle11.2odacbin; at the beginning in the “Variable value” TextBox
Step 5.6: Close all the windows by clicking on “OK” button
Step 6: Restart your system.
You can now connect to your Oracle Database from PowerApps, Flow and Logic Apps. The Oracle Database connection allows you to list tables, and perform standard create, read, update and delete of rows in an Oracle databases. In addition, it supports full delegation of PowerApps’ filtering, sorting and other functions. It does not support triggers or store procedures yet.
Supported versions: Oracle 9 and later, Oracle client software 8.1.7 and later.
The Oracle Database connection requires the installation of the on-premises data gateway and Oracle client SDK.
Install On-premises Data Gateway
The Oracle Database connection requires the On-premises data gateway to be installed. Based on the service that you want to connect to, you can follow the steps defined below to install:
- Flow: https://ms.flow.microsoft.com/en-us/documentation/gateway-manage/
- PowerApps: https://powerapps.microsoft.com/en-us/tutorials/gateway-management/
- Logic Apps: https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-gateway-connection
A note about the on-premises data gateway: The on-premises data gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that is not in the cloud) and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services. The same gateway can be used with multiple services and multiple data sources. So, you will probably need to do this only once. More about gateways can be found here (https://powerapps.microsoft.com/en-us/tutorials/gateway-reference/).
Install Oracle Client
To connect to Oracle, you will also need to install the Oracle client software on the machine where you have installed the on-premises data gateway. Specifically, the Oracle Database connection requires the installation of the 64-bit Oracle Data Provider for .NET. You can use the following link to download and install the Oracle client:
If you do not have the Oracle client installed, you will see an error when you try to create or use the connection (see the known issues below).
Create an app using Oracle Database connection
Depending on the service you are using, follow the appropriate steps to create a connection. Here, we will use PowerApps, but the steps for Flow and Logic Apps should be similar. Even with PowerApps, there are multiple ways in which you can start using the connection. In this walkthrough, we will use the PowerApps’s feature of creating an app from an Oracle database table.
Follow the steps below to create an app from an Oracle database in PowerApps:
- Open PowerApps Studio, sign in if it prompts you to, and click New to create a new app.
- Under “Start with your data”, click on the arrow which will show you a list of connections you already have. Click on “+New connection” to specify that you want to create a new connection.
- Under the list of connections, select “Oracle Database”.
- On the right side, specify the Oracle server name, username and password. For the server, if an SID is required, you can specify this in the format: ServerName/SID.
- As mentioned above, you will need to use a Gateway to use an Oracle Database connection. Select the appropriate gateway. If required, you can also install a gateway. Click on “Refresh gateway list” if you do not see your gateway.
- Click “Create” to create the connection. If everything is fine, you will see the screen to select a dataset with a single dataset called “default”.
- Select default.
- You will see a list of tables based on the Oracle database you are connecting to. Select the table that you want to use.
- Click “Connect” to create the app
Based on the table that you selected, PowerApps will create an app for you with three screens:
- A Browse screen that list all the entries in the table
- A Detail screen that provides additional info about an entry
- An Edit screen that can be used for updating an existing entry or creating a new entry
This is just the starting point. You may want to customize these screens, add more tables into your app to finish up your app. You can refer to PowerApps documentation for more information.
Creating a flow using the Oracle Database connection
Oracle Client Software Version 8.1 7 Or Greater Free Download 64-bit
You can use the steps for Flow and Logic Apps to create a flow or a logic app and use the Oracle Database connection. I will not go into the details here, but the steps for creating the connection should be very similar to the steps listed above. The Oracle Database connection does not yet support any triggers. So, use another trigger at the start of your workflow. In a logic app, for example, you can use the Request/Response trigger to start your logic app, and then add the Oracle Database connection to see its actions. Once you have a connection, you can use any of the actions listed below.
Known Issues, Tips, and Troubleshooting
Here are some common issues, limitations and workarounds:
- Cannot reach the Gateway.
If you get this error “Cannot reach the Gateway”, it means that on-premises data gateway is not able to connect to the cloud. To check the status of your Gateway, you can login to powerapps.microsoft.com. Click Gateways, and select/click the Gateway you are trying to connect to.
Mitigation: Make sure your gateway is running on the on-premise machine where it is installed, and that it can connect to the internet. We recommend against installing the gateway on a computer that may be turned off or sleep. Try restarting the on-premises data gateway service (PBIEgwService) as well.
- The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the official provider.
You get the above error if the Oracle client SDK is not installed on the machine where the on-premises data gateway is running. To fix this issue, download and install the Oracle client SDK as given above.
- Table '[Tablename]' does not define any key columns
This error message signifies that the table it is connecting to, does not have any primary key. Currently, the Oracle Database connection requires that a table with a primary key column be used.
- Stored Procedures are currently not supported. We are looking at adding this functionality in the future. We would like you to vote for it in the community forum if you are interested.
- Any table with composite keys are not supported. We would like you to vote for it in the community forum if you are interested.
- Nested object types in tables are not supported. We would like you to vote for it in the community forum if you are interested.
Oracle Client Software Version 8.1 7 Or Greater Free Download 32-bit
The Oracle Database connection is one of the connectors that many of our users have asked for. We hope to get some usage and feedback on this. Please feel free to use the community forums to provide us your bug reports, feedback and ideas.