Oracle

Oracle database is a multi-model database management system by Oracle Corporation. It can be hosted on-premise or in a private cloud.

Supported editions

All releases of Oracle database are supported.

How to connect to Oracle on Workato

The Oracle connector uses basic authentication to authenticate with Oracle. Configured Oracle connection

Field Description
Connection name Give this Oracle connection a unique name that identifies which Oracle instance it is connected to.
On-prem secure agent Choose an on-premise agent if your database is running in a network that does not allow direct connection. Before attempting to connect, make sure you have an active on-premise agent. Refer to the On-premise agent guide for more information.
Username Username to connect to Oracle.
Password Password to connect to Oracle.
Host URL of your hosted server.
Port Port number that your server is running on, typically 1521.
SID/Service name SID or Service name of your Oracle database instance you wish to connect to.

Permissions required to connect

At minimum, the database user account must be granted SELECT permission to the database specified in the connection. 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 will 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)

Working with the Oracle connector

Table, view and stored procedure

After successfully connecting to your Oracle database and selecting an action/trigger in your recipe, you will often be prompted to select either a table, view or stored procedure. This tells Workato where to pull or send data to.

Tables and Views

The Oracle connector works with all tables and views. These are available in pick lists in each trigger/action or you can provide the exact name. Views can be called using this as well and be used in the same way as a table.

Table selection from pick list

Select a table/view from pick list


Exact table name provided

Provide exact table/view name in a text field

Stored Procedures

Stored procedures are custom written workflows that have to be written and saved within your Oracle database. They are able to do a range of functionalities including creating, reading, updating and deleting rows. They can also accept parameters. Click here if you want to know more about how Workato works with stored procedures.

Using WHERE conditions

This input field is used to filter and identify rows to perform an action on. It is used in multiple triggers and actions in the following ways:

  • filter rows to be picked up in triggers
  • filter rows in Select rows action
  • filter rows to be deleted in Delete rows action

This clause will be used as a WHERE statement in each request. This should follow basic SQL syntax. Refer to this Oracle documentation for a full list of rules for writing Oracle statements.

Operators

At the foundation of any WHERE statement, we have operators that help us filter and identify what rows we want returned in Workato. By chaining operators in the same way one would do it in SQL, you'll be able to use them to create robust and complex filters on your data directly from Workato.

Click here for a list of operators
Operator Description Example
= Equal WHERE ID = 445
!=
<>
Not equal WHERE ID <> 445
>
>=
Greater than
Greater than or equal to
WHERE PRICE > 10000
<
<=
Less than
Less than or equal to
WHERE PRICE > 10000
IN(...) List of values WHERE ID IN(445, 600, 783)
LIKE Pattern matching with wildcard characters (% and _) WHERE EMAIL LIKE '%@workato.com'
BETWEEN Retrieve values with a range WHERE ID BETWEEN 445 AND 783
IS NULL
IS NOT NULL
NULL values check
Non-NULL values check
WHERE NAME IS NOT NULL

Data types

The other component of a WHERE condition would be to use these operators in conjunction with the proper datatypes. When writing WHERE statements, make sure you compare a variable of data type = integer in your table with a variable of data type = integer instead of data type = string.

Workato also helps reveal the data types expected for each input field when you select

  • Update rows actions
  • Upsert rows actions

They appear directly below the output field, allowing you to know the expected data type to be sent while building the recipe. Use these hints to send the proper data types over to your Oracle database as failing to do so might lead to unexpected behaviour or failed jobs.

input field hints

Hints below each input field inform you about the data type expected

Here are some of the common data types you can expect to see. A more comprehensive list can be found here

Click here for a list of common data types
Data type Description Example
number The NUMBER data type stores zero, positive and negative fixed numbers. -100,1,30,000
FLOAT The FLOAT data type is a subtype of NUMBER. You can can specify it with or without precision. Scale cannot be specified, but is interpreted from the data. Each FLOAT value requires from 1 to 22 bytes. 1.11,2.0761,1.61803398875
YEAR valid values are -4712 to 9999 excluding year 0 1,245,100
MONTH 01-12 1
DAY 01-31 1,0,15
VARCHAR2(n) Variable width character string of length n Foo_bar
nchar(n) Fixed width character string of length n Foo where n = 3
TIMESTAMP From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 2011-09-16 13:23:18.767

Writing WHERE conditions

Now that we've gone through operators and data types, we are ready to write our WHERE conditions. String values must be enclosed in single quotes ('') and columns used must exist in the table/view.

A simple WHERE condition to filter rows based on values in a single column looks like this.

currency = 'USD'

If used in a Select rows action, this WHERE condition will return all rows that have the value 'USD' in the currency column. Just remember to wrap datapills with single quotes in your inputs.

Using datapills in WHERE condition

Using datapills in `WHERE` condition

Column names that do not conform to standard rules (includes spaces, lower-case letters or special characters) must be enclosed in double quotes (""). For example, PUBLISHER NAME must be enclosed in backquotes to be used as a valid identifier.

"PUBLISHER NAME" = 'USD'

WHERE condition with enclosed identifier

`WHERE` condition with enclosed identifier

Click the tabs below for more details into the functionality you can explore with your WHERE conditions.

Using AND and OR in your WHERE conditions WHERE conditions can also be used in conjunction with basic SQL logical operators like AND and OR to add more filters on the rows you return.
("currency code" = 'USD' AND totalAmt >1000) OR totalAmt>2000
When used together, this WHERE condition will return all rows that either have the value 'USD' in the currency code column AND more than 1000 in the totalAmt column OR more than 2000 in the totalAmt column
Using sub-queries in your WHERE conditions Your WHERE condition can also contain subqueries. The following query can be used on the users table.
ID IN (SELECT "USER ID" FROM TICKETS WHERE PRIORITY >= 2)
When used in a Delete rows action, this will delete all rows in the users table where at least one associated row in the tickets table has a value of 2 in the priority column. Using datapills in WHERE condition with subquery
Using datapills in WHERE condition with subquery

Configuring triggers

Oracle connector has triggers for both new and updated rows. For the triggers to work, both Unique keys must be configured. Sort columns need to be configured for triggers that find recently updated rows.

A table must satisfy some constraints to be used in a trigger. The following sections contain more information about specific constraints. Read our best practices to find out how you can prepare your table for use with Workato

Unique keys

In all triggers and some actions, this is a required input. Values from this selected column are used to uniquely identify rows in the selected table. As such, the values in the selected column must be unique. Typically, this column is the primary key of the table (e.g. ID).

When used in a trigger, this column must be incremental. This constraint is required because the trigger uses values from this column to look for new rows. In each poll, the trigger queries for rows with a unique key value greater than the previous greatest value.

Click here for a detailed example Let's use a simple example to illustrate this behavior. We have a New row trigger that processed rows from a table. The unique key configured for this trigger is ID. The last row processed has 100 as it's ID value. In the next poll, the trigger will use >= 101 as the condition to look for new rows. Performance of a trigger can be improved if the column selected to be used as the unique key is indexed.

Sort column

This is required for New/updated row triggers. Values in this selected column are used to identify updated rows.

When a row is updated, the Unique key value remains the same. However, it should have it's Sort column updated to reflect the last updated time. Following this logic, Workato keeps track of values in this column together with values in the selected Unique key column. When a change in the Sort column value is observed, an updated row event will be recorded and processed by the trigger.

For Oracle database, only date, timestamp, timestamp with time zone and timestamp with local time zone column types can be used.

Click here for a detailed example Let's use a simple example to illustrate this behavior. We have a new/updated row trigger that processed rows from a table. The Unique key and Sort column configured for this trigger is ID and UPDATED_AT respectively. The last row processed by the trigger has ID value of 100 and UPDATED_AT value of 2018-05-09 16:00:00.000000. In the next poll, the trigger will query for new rows that satisfy either of the 2 conditions:
1. UPDATED_AT'2018-05-09 16:00:00.000000'
2. ID > 100 AND UPDATED_AT = '2018-05-09 16:00:00.000000'

Using single row actions/triggers vs using batch of rows actions/triggers

Oracle connector can read or write to your database either as a single row or in batches. When using batch triggers/actions, you have to provide a batch size you wish to work with. The batch size can be any number between 1 and 100, with 100 being the maximum size limit. Batch triggers and actions are great for jobs when you expect to read, create or update a large number of rows. Choosing to batch your job runs rather than having them split into separate jobs runs not only saves operations but reduces recipe runtimes and decreases load on your servers.

Batch trigger inputs

Batch trigger inputs

Besides the difference in input fields, there is also a difference between the outputs of these 2 types of operations. A trigger that processes rows one at a time will have an output datatree that allows you to map data from that single row.

Single row output

Single row output

However, a trigger that processes rows in batches will output them as an array of rows. The Rows datapill indicates that the output is a list containing data for each row in that batch.

Batch trigger output

Batch trigger output

As a result, the output of batch triggers/actions needs to be handled differently. This recipe uses a batch trigger for new rows in the users table. The output of the trigger is used in a Salesforce bulk upsert action that requires mapping the Rows datapill into the source list.

Using batch trigger output

Using batch trigger output

Outputs from batch triggers/actions can also be used outside of actions that work specifically with lists. By using Workato's repeat step, you'll be able to control batch outputs and use them with any action built for single rows..

Unsure when to use batch actions and single row actions? Check out our best practices section for recipe design tips!

Smart boolean conversion

Oracle does not have a built-in boolean column type. A popular workaround is to use a NUMBER(1,0) column with a CHECK (COLUMN_NAME IN(1,0)) constraint. Because of this, standard values from other applications will not map well to this column and may cause unexpected values or errors.

This checkbox allows you to enable automatic smart conversion. If this is set to Yes, the conversion will be applied to all columns with NUMBER type and precision of 1. This reduces the amount of configuration needed to transform datapills in a recipe. The following table describes the logic for the boolean conversion.

Input value Converted value
true 1
false 0
"true" 1
"false" 0

List of Workato triggers and actions

Workato currently supports the following triggers and actions. Find out more details about each by clicking on the links below. You can also navigate to them through the side bar.

results matching ""

    No results matching ""