# Connecting Oracle to Workato

In this guide, we'll show you how to connect your Oracle database to your Workato workspace.

The Oracle connector uses basic authentication to authenticate.

# Prerequisites

To connect Oracle, you'll need:

A database user with read and write privileges on the database you're connecting, OR
Privileges that allow you to create database users and assign privileges

# Step 1: Set up an Oracle database user

At a minimum, the database user account must be granted SELECT permission to the database specified in the connection settings. Check out the example below to find out more about how to set permissions if you are the one setting up the Oracle server connection for your business.

Click here to find out how to set up permissions

If we are trying to connect to a named schema (HR_PROD) in an Oracle instance, using a new database user WORKATO, the following example queries can be used.

First, create a new user dedicated to integration use cases with Workato.
CREATE USER WORKATO IDENTIFIED BY password
Next, grant CONNECT to this user.
GRANT CONNECT TO WORKATO;
This allows the user to have login access to the Oracle instance. However, this user does not have access to any tables.

The next step is to grant access to SUPPLIER table in the HR_PROD schema. In this example, we only wish to grant SELECT and INSERT permissions.
GRANT SELECT,INSERT ON HR_PROD.SUPPLIER TO WORKATO;
Finally, check that this user has the necessary permissions. Run a query to see all grants.
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'WORKATO';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'WORKATO';
This should return the following minimum permission to create a Oracle connection on Workato.
+---------+--------------+--------------+--------------+
| GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DEFAULT_ROLE |
+---------+--------------+--------------+--------------+
| WORKATO | CONNECT      | NO           | YES          |
+---------+--------------+--------------+--------------+>

+---------+---------+------------+---------+-----------+-----------+-----------+
| GRANTEE | OWNER   | TABLE_NAME | GRANTOR | PRIVILEGE | GRANTABLE | HIERARCHY |
+---------+---------+------------+---------+-----------+-----------+-----------+
| WORKATO | HR_PROD | SUPPLIER   | ROOT    | SELECT    | NO        | NO        |
| WORKATO | HR_PROD | SUPPLIER   | ROOT    | INSERT    | NO        | NO        |
+---------+---------+------------+---------+-----------+-----------+-----------+
3 rows in set (0.61 sec)

# Step 2: Define the connection settings in Workato

Complete the following fields in Workato to set up the connection:

Field	Description
Connection name	Give the connection a unique name that identifies which Oracle instance it is connected to.
Database host	The URL of your hosted server.
Database port	The number of the port the server is running on, typically `1521`.
User name	The database user's username from Step 1.
Password	The database user's password.
Database name	The SID or Service name of the Oracle database instance you wish to connect to.
Advanced settings	Contains advanced connection settings such as improved datetime handling and ability to set database timezone. Refer to the next section for more info.
Is this app in a private network?	If your database is running in a network that doesn't allow direct connections, choose an on-premise agent. Before attempting to connect, make sure you have an active on-premise agent. Refer to the On-premise agent guide for more information.

# Step 3: Configure advanced settings

TIP

While this step is optional, we strongly recommend defining the Use improved datetime handling settings. These settings allow you to set your local database timezone and improve datetime timezone conversion during insertion.

Oracle connectors have the following advanced settings:

Field	Description
Use improved datetime handling	Overrides the default database timezone of `UTC`. If enabled, `date` and `timestamp` data types are converted during insertion to the Database timezone you provide. Note: This setting only affects Insert actions.
Database timezone	Defines the local timezone of your database and requires Use improved datetime handling to be enabled. If not defined, Workato uses `UTC` as the timezone. Additionally, if Use improved datetime handling is not enabled, this setting is not respected. Note: This setting only affects Insert actions.

Let's look at some examples of how these settings affect how datetime data is handled in Insert actions.

All examples assume the Database timezone is set to UTC -04:00 (Eastern Standard Time).

Source value	Destination column	Destination value
`2020-12-02 10:00:00` Source value doesn't have a timezone component	NOT timezone capable (DATE or TIMESTAMP)	`2020-12-02 10:00:00` Value is inserted as-is, as conversion is unnecessary
`2020-12-02 10:00:00` Source value doesn't have a timezone component	Timezone capable (TIMESTAMP WITH TIME ZONE)	`2020-12-02 10:00:00 -04:00` Value has Database timezone applied
`2020-12-02 10:00:00 -07:00` Source value has a timezone component, but it's not the same as the Database timezone (`UTC -04:00`)	NOT timezone capable (DATE or TIMESTAMP)	`2020-12-02 13:00:00` Value is converted using the Database timezone
`2020-12-02 10:00:00 -07:00` Source value has a timezone component, but it's not the same as the Database timezone (`UTC -04:00`)	Timezone capable (TIMESTAMP WITH TIME ZONE)	`2020-12-02 13:00:00 -04:00` Value is converted using the Database timezone

# Step 4: Complete the connection

When finished, click Connect to complete the connection. You're done!

# What's next?

Learn more about the Oracle connector:

Last updated: 12/8/2023, 8:23:23 PM