Setting Up An Oracle Connection With Arist


Caleb Belanger
Last Updated: 2 months ago

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:

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!


Was this article helpful?