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
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.
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:
- 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.
- Oracle Functions are not supported so they are not listed in the UI.
- The response size limit is 8MB.
- The request size limit is 2MB.
- 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.
- The following data types cannot 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 will timeout.
- Insert and update to a table do not return the full item, they return only the input properties for the operation.
- A Primary Key is required to get deterministic paging results for the GetRows operation.
- Insert row operation requires to provide explicit value for Primary Key column, even though default/autoincrement value is defined.
- 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.
- If latency is observed while working with the Oracle database, the recommendation is to install the latest 64-bit Oracle Data Access Components (ODAC). This will need to be installed on the gateway machine (or on each gateway machine, if the gateway is a cluster).
- If an Oracle connection creation fails with a timeout, please try to use an Oracle credential with minimal permission, therefore the connection would be more light-weight and therefore it may avoid experiencing the timeout during connection creation.
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 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 |