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/ |
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.
- To use this connector in Power Apps, see Connect to an Oracle database from Power Apps.
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.
For Microsoft Power Apps, see also Manage an on-premises data gateway in Power Apps.
For Azure Logic Apps, you must also create an Azure gateway resource for your gateway installation before you can use the connector. For more information, see Connect to on-premises data from logic apps.
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
xcopyversion 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
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
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
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
Get tables
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
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
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 |