Oracle Database

Oracle Database is a relational database management system developed by Oracle. Connect to on-premise Oracle Database to perform various actions such as create, update, get, and delete on rows in a table.

This connector is available in the following products and regions:

Service Class Regions
Logic Apps Standard All Logic Apps regions except the following:
     -   US Department of Defense (DoD)
Power Automate Premium All Power Automate regions
Power Apps Premium All Power Apps regions
Contact
Name Microsoft
URL Microsoft LogicApps Support
Microsoft Power Automate Support
Microsoft Power Apps Support
Connector Metadata
Publisher Microsoft
Website https://www.oracle.com/database/
Privacy policy https://www.oracle.com/legal/privacy/

To use this connector in Power Apps, learn more here.

What's new

The following are new updates and capabilities of Oracle connector

January 2018

  • Support Oracle view as read-only table

October 2018

  • Support Oracle Stored Procedure

Prerequisites

  • Supported Oracle versions:
    • Oracle 9 and later
    • Oracle Data Access Client (ODAC) 11.2 and later
  • Install the on-premises data gateway. Connect to on-premises data from logic apps lists the steps. The gateway is required to connect to an on-premises Oracle Database, or an Azure VM with Oracle DB installed.

Note

The on-premises data gateway acts as a bridge, and provides a secure data transfer between on-premises data (data that is not in the cloud) and your logic apps. The same gateway can be used with multiple services, and multiple data sources. So, you may only need to install the gateway once.

  • Install the Oracle Client on the machine where you installed the on-premises data gateway. Make sure that you install the 64-bit Oracle Data Provider for .NET from Oracle, and select the Windows installer version because the xcopy version doesn't work with the on-premises data gateway: 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64. You can verify installation by running following PowerShell command: [System.Data.Common.DbProviderFactories]::GetFactoryClasses(). If an Oracle Client is installed, you should see "OracleClient Data Provider" in the results.

Tip

If the Oracle client is not installed, an error occurs when you try to create or use the connection. See the common errors in connector in-depth section.

  • On your Oracle client, set the environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE before you run any connector operations. This setting enables NCHAR string literal replacement, which prevents data loss when inserting string literals that contain characters beyond the database character set into the NCHAR/NVARCHAR/NCLOB type column. String literals are converted to an internal format and then are properly decoded to Unicode when the statement executes.

Known issues and limitations

The followings are some of the known limitations of using Oracle connector:

  1. When invoking a Stored Procedure on an Oracle server, we have the following limitations:
    • OUT parameters are not supported currently.
    • Return value is not available since Oracle Stored Procedure does not return any result.
  2. Oracle Functions are not supported so they are not listed in the UI.
  3. The response size limit is 8MB.
  4. The request size limit is 2MB.
  5. Oracle native query is supported with the following limitations:
    • RefCursor is not supported.
    • OUT parameters are not supported.
    • Only one result set can be returned.
    • Gateway version 3000.63.4 (October release) or later is required.
  6. The following data types cannot be used as query option predicates:
    • DATE
    • TIMESTAMP
    • TIMESTAMP with timezone
    • TIMESTAMP with local timezone
  7. If any Oracle query or stored procedure execution time exceeds 110 seconds, the action will timeout.
  8. Insert and update to a table do not return the full item, they return only the input properties for the operation.
  9. A Primary Key is required to get deterministic paging results for the GetRows operation.
  10. Insert row operation requires to provide explicit value for Primary Key column, even though default/autoincrement value is defined.
  11. Due to UI component limitations - list of tables rendered during design time can't show more than 2048 elements, which is default page size for Get tables operation. Examples:
    • List of tables for 'Table name' Get rows action parameter.
    • List of tables for 'Create connection/Choose a table' flow for PowerApps designer.

Connector in-depth

For more information about the connector, see the in-depth section.

General Limits

Name Value
Maximum number of requests being processed by the connector concurrently per connection 200
Maximum number of megabytes being transferred through the connector concurrently per connection 30

Creating a connection

The connector supports the following authentication types:

Default Parameters for creating connection. All regions Shareable

Default

Applicable: All regions

Parameters for creating connection.

This is shareable connection. If the power app is shared with another user, connection is shared as well. For more information, please see the Connectors overview for canvas apps - Power Apps | Microsoft Docs

Name Type Description Required
Server string Specify the Oracle Server. If a port or SID is required, specify in the form of ServerName:Port/SID. True
Authentication Type [Select Basic] string Authentication type to connect to your database
Username securestring Username credential True
Password securestring Password credential True
Gateway gatewaySetting On-prem gateway (see https://docs.microsoft.com/data-integration/gateway for more details True

Throttling Limits

Name Calls Renewal Period
API calls per connection150060 seconds

Actions

Delete row

This operation deletes a row from a table.

Execute a Oracle query

Execute a Oracle query

Execute stored procedure

This operation runs a stored procedure.

Get row

This operation gets a row from a table.

Get rows

This operation gets rows from a table.

Get tables

This operation gets tables from a database.

Insert row

This operation inserts a new row into a table.

Update row

This operation updates an existing row in a table.

Delete row

This operation deletes a row from a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of table

Row id
id True string

Unique identifier of the row to delete

Execute a Oracle query

Execute a Oracle query

Parameters

Name Key Required Type Description
Query
query True dynamic

query body

Returns

The outputs of this operation are dynamic.

Execute stored procedure

This operation runs a stored procedure.

Parameters

Name Key Required Type Description
Procedure name
procedure True string

Name of stored procedure

Parameters list
parameters True dynamic

Input parameters to the stored procedure

Returns

The outputs of this operation are dynamic.

Get row

This operation gets a row from a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of Oracle table

Row id
id True string

Unique identifier of the row to retrieve

Returns

The outputs of this operation are dynamic.

Get rows

This operation gets rows from a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of Oracle table

Filter Query
$filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

Order By
$orderby string

An ODATA orderBy query for specifying the order of entries.

Top Count
$top integer

Total number of entries to retrieve (default = all).

Skip Count
$skip integer

The number of entries to skip (default = 0).

Select Query
$select string

Specific fields to retrieve from entries (default = all).

Returns

The outputs of this operation are dynamic.

Get tables

This operation gets tables from a database.

Returns

Represents a list of tables.

Body
TablesList

Insert row

This operation inserts a new row into a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of table

Row
item True dynamic

Row to insert into the specified table

Returns

The outputs of this operation are dynamic.

Update row

This operation updates an existing row in a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of table

Row id
id True string

Unique identifier of the row to update

Row
item True dynamic

Row with updated values

Returns

The outputs of this operation are dynamic.

Definitions

Table

Represents a table.

Name Path Type Description
Name
Name string

The name of the table. The name is used at runtime.

DisplayName
DisplayName string

The display name of the table.

DynamicProperties
DynamicProperties object

Additional table properties provided by the connector to the clients.

TablesList

Represents a list of tables.

Name Path Type Description
value
value array of Table

List of Tables