Connecting to Oracle Database from PowerApps, Flow and Logic Apps

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.

Pre-requisites

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:

• 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64

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:

1. Open PowerApps Studio, sign in if it prompts you to, and click New to create a new app.
   
2. 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.
   
3. Under the list of connections, select “Oracle Database”.
   
4. 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.
   
5. 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.
   
6. 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”.
   
7. Select default.
   
8. You will see a list of tables based on the Oracle database you are connecting to.  Select the table that you want to use.
   
9. 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

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. Any table with composite keys are not supported.  We would like you to vote for it in the community forum if you are interested.
6. Nested object types in tables are not supported.  We would like you to vote for it in the community forum if you are interested.

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.