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 userWORKATO
, 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 theHR_PROD
schema. In this example, we only wish to grantSELECT
andINSERT
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 |
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 |
Database timezone | Defines the local timezone of your database and requires Use improved datetime handling to be enabled. If not defined, Workato uses |
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 |
---|---|---|
| NOT timezone capable (DATE or TIMESTAMP) |
|
| Timezone capable (TIMESTAMP WITH TIME ZONE) |
|
| NOT timezone capable (DATE or TIMESTAMP) |
|
| Timezone capable (TIMESTAMP WITH TIME ZONE) |
|
Step 4: Complete the connection
When finished, click Connect to complete the connection. You're done!