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
Connector Metadata
Publisher Microsoft
Website https://www.oracle.com/database/
Privacy policy https://www.oracle.com/legal/privacy/

This article describes the operations for the Oracle Database connector, which is available for Azure Logic Apps, Microsoft Power Apps, and Microsoft Power Automate. You can use this connector to connect with an on-premises Oracle Database or an Azure virtual machine with Oracle Database installed.

What's new

The following table describes the new updates and capabilities for the Oracle Databaseconnector:

Update Description
October 2018 Supports Oracle stored procedure.
January 2018 Supports Oracle view as read-only table.

Prerequisites

  • Supported Oracle versions:

    • Oracle 9 and later
    • Oracle Data Access Client (ODAC) 11.2 and later
  • To connect with an on-premises Oracle database or Azure virtual machine with Oracle Database installed, you must install the on-premises data gateway.

    This gateway acts as a bridge and provides a secure data transfer between on-premises data and your app or client. You can use the same gateway installation with multiple services and data sources, which means you might only need to install the gateway once.

  • Install your Oracle client on the computer 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.

    Note

    If the Oracle client isn't installed, an error occurs when you try to create or use the connection. For more information, see the Common connection errors section.

  • Before you run any connector operations, on your Oracle client, set the environment variable named ORA_NCHAR_LITERAL_REPLACE to TRUE.

    This setting enables NCHAR string literal replacement, which prevents data loss when string literals that contain characters beyond the database character set are inserted into the NCHAR/NVARCHAR/NCLOB type column. When the statement executes, string literals are converted to an internal format and are then properly decoded to Unicode.

Known issues and limitations

The following list describes some known limitations for the Oracle Datbase connector:

  • Not supported:

    • Any table with composite keys
    • Nested object types in tables
    • Database functions with non-scalar values
    • Oracle Functions so they're not listed in the UI.
  • When you invoke a stored procedure on an Oracle server, the following limitations apply:

    • OUT parameters aren't supported.
    • The return value isn't available because the stored procedure doesn't return any results.
  • The response size limit is 8MB.

  • The request size limit is 2MB.

  • Oracle native query is supported with the following limitations:

    • Requires gateway version 3000.63.4 (October release) or later.
    • RefCursor isn't supported.
    • OUT parameters aren't supported.
    • Only one result set can be returned.
  • The following data types can't be used as query option predicates:

    • DATE
    • TIMESTAMP
    • TIMESTAMP with timezone
    • TIMESTAMP with local timezone
  • If any Oracle query or stored procedure execution time exceeds 110 seconds, the action times out.

  • Insert and update to a table don't return the full item. They return only the input properties for the operation.

  • To get deterministic paging results for the Get rows operation, a Primary Key is required.

  • The Insert row operation requires that you provide an explicit value for Primary Key column, even though default/autoincrement value is defined.

  • Due to UI component limitations, the list of tables rendered during design time can't show more than 2,048 elements, which is default page size for the Get tables operation, for example:

    • List of tables for the Get rows action parameter named 'Table name'.
    • List of tables for the 'Create connection/Choose a table' flow in the Power Apps designer.
  • If you observe latency while working with the Oracle database, install the latest 64-bit Oracle Data Access Components (ODAC) on the gateway machine or each gateway machine, if the gateway is a cluster.

  • If Oracle connection creation fails with a timeout, try using an Oracle credential with minimal permission. That way, the connection is more lightweight, which might help you avoid experiencing the timeout during connection creation.

Common connection errors

Error: Cannot reach the Gateway

Cause: The on-premises data gateway can't connect to the cloud.

Mitigation: Make sure your gateway is running on the on-premises computer where you installed the gateway and has internet connectivity. Avoid installing the gateway on a computer that might be turned off or go to sleep. You can also try restarting 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 https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the official provider.

Cause: The Oracle client SDK isn't installed on the computer 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 columns

Cause: The table doesn't have a primary key.

Resolution: The Oracle Database connector requires that you use a table with a primary key column.

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 connection 1500 60 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