Get started with the Oracle Database connector. 4 minutes to read.In this articleUsing the Oracle Database connector, you create organizational workflows that use data in your existing database. This connector can connect to an on-premises Oracle Database, or an Azure virtual machine with Oracle Database installed. With this connector, you can:.
Build your workflow by adding a new customer to a customers database, or updating an order in an orders database. Use actions to get a row of data, insert a new row, and even delete. For example, when a record is created in Dynamics CRM Online (a trigger), then insert a row in an Oracle Database (an action).This article shows you how to use the Oracle Database connector in a logic app. Prerequisites.Supported Oracle versions:.
Oracle 9 and later. Oracle client software 8.1.7 and later.Install the on-premises data gateway. Lists the steps. The gateway is required to connect to an on-premises Oracle Database, or an Azure VM with Oracle DB installed.
TipThis is also the quickest way to see the triggers and actions available for any connector. Type in part of the connector name, such as oracle. The designer lists any triggers and any actions.Select one of the actions, such as Oracle Database - Get row. Select Connect via on-premises data gateway. Enter the Oracle server name, authentication method, username, password, and select the gateway:.Once connected, select a table from the list, and enter the row ID to your table. You need to know the identifier to the table.
If you don't know, contact your Oracle DB administrator, and get the output from select. from yourTableName. This gives you the identifiable information you need to proceed.In the following example, job data is being returned from a Human Resources database:.In this next step, you can use any of the other connectors to build your workflow. If you want to test getting data from Oracle, then send yourself an email with the Oracle data using one of the send email connectors, such Office 365 or Gmail. Use the dynamic tokens from the Oracle table to build the Subject and Body of your email:.Save your logic app, and then select Run. Close the designer, and look at the runs history for the status. If it fails, select the failed message row.
The designer opens, and shows you which step failed, and also shows the error information. If it succeeds, then you should receive an email with the information you added.Workflow ideas.You want to monitor the #oracle hashtag, and put the tweets in a database so they can be queried, and used within other applications. In a logic app, add the Twitter - When a new tweet is posted trigger, and enter the #oracle hashtag. Then, add the Oracle Database - Insert row action, and select your table:.Messages are sent to a Service Bus queue. You want to get these messages, and put them in a database. In a logic app, add the Service Bus - when a message is received in a queue trigger, and select the queue.
Then, add the Oracle Database - Insert row action, and select your table:Common errors Error: Cannot reach the GatewayCause: The on-premises data gateway is not able to connect to the cloud.Mitigation: Make sure your gateway is running on the on-premises machine where you installed it, and that it can connect to the internet. We recommend not installing the gateway on a computer that may be turned off or sleep. You can also restart the on-premises data gateway service (PBIEgwService). Error: The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.' See to install the official provider.Cause: The Oracle client SDK is not installed on the machine where the on-premises data gateway is running.Resolution: Download and install the Oracle client SDK on the same computer as the on-premises data gateway. Error: Table 'Tablename' does not define any key columnsCause: The table does not have any primary key.Resolution: The Oracle Database connector requires that a table with a primary key column be used. Currently not supported. Views.
Any table with composite keys. Nested object types in tablesConnector-specific detailsView any triggers and actions defined in the swagger, and also see any limits in the. Get some helpThe is a great place to ask questions, answer questions, and see what other Logic Apps users are doing.You can help improve Logic Apps and connectors by voting and submitting your ideas at. Next steps, and explore the available connectors in Logic Apps at.
Oracle Workflow Installation Steps
About the Oracle Client DirectoryThis directory was generated by the Alteryx install script, and contains an instance of the Oracle Instant Client. Oracle Instant Client is a collection of installed Oracle Database libraries for connecting Alteryx Designer to local or remote Oracle Databases. There’s no need for tnsnames.ora file.The Oracle client setting is located in your UserSettings.xml file, located in C:Users%USERNAME%AppDataRoamingAlteryxEngine. This file is used to load your Designer settings during startup.
How To Install Oracle Xml Gateway Message Designer
You can open this file in notepad and look for the. In my case, it looks something like this:You can use this to check the location of your Oracle Instant Client or install a new one as well. To install a new one, you can:.
Find the instantclient121 folder (from the UserSettings.xml file) and put it in a different location Restart Alteryx Designer Try to connect to Oracle in Designer and you should receive a message “Oracle Client Not Found”. From here, you can re-install the client.