We connect to Oracle using a third-party tool call Workato which allows us to use credentials to set up a secure connection and sync Learner attributes to Arist.

To connect we require the following credentials:

• Database Host Address

• Database Port

• Username

• Password

• Database Name

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.

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:

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:

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).

Step 4: Complete the connection

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