Skip to main content

Oracle DB

ToolJet can connect to Oracle databases to read and write data.

Connection​

To establish a connection with the Oracle DB Datasource, you can either click on the + Add new Data source button located on the query panel or navigate to the Data sources page through the ToolJet dashboard.

Select the Connection mode: Basic Connection, Thick Mode (TNS/Wallet File Path) or Thin Mode (TNS/Wallet Zip Upload). The fields for each mode are described below.

Basic Connection​

ToolJet requires the following to connect to an Oracle DB datasource:

  • Username
  • Password
  • Host
  • Port
  • SID / Service name: Select SID or Service name.
  • Database name: The SID or service name to connect to.
  • Client library location: Select Default to use an Oracle Instant Client bundled with ToolJet, then choose the Instant client version. Select Custom to use your own Oracle Instant Client, then enter its directory in Path.
  • SSL: Turn on to connect using SSL.
note

Enter either a SID or a Service Name depending on your Oracle configuration. A SID identifies a specific database instance, while a Service Name identifies a database service and is commonly used in modern Oracle deployments.

Data source-OracleDB-Connection

Client Versions and Compatibility​

Basic Connection and Thick Mode connect in Oracle thick mode, which needs an Oracle Instant Client. Thin Mode doesn't need a client library. ToolJet includes the following Oracle Instant Client versions, which you can select in Instant client version when Client library location is Default:

  • Oracle Instant Client 21.10 – Supports Oracle Database 11.2 and later.
  • Oracle Instant Client 11.2 – Supports Oracle Database 10.2 and later.
info

If your environment requires a different Oracle Instant Client version, set Client library location to Custom and enter the directory that contains the client library in Path. The directory must be available on the ToolJet server, for example by mounting it into the deployment.

Thick Mode ( TNS/Wallet File Path)​

The following are required to make a Thick Mode Connection with Oracle DB.

  • Username
  • Password
  • TNS alias
  • Configuration directory
  • Oracle wallet path
  • Wallet password
  • Client library location: Works the same way as in Basic Connection.
Data source-OracleDB-Connection

Thin Mode ( TNS/Wallet Zip Upload)​

The following are required to make a Thin Mode Connection with Oracle DB.

  • Username
  • Password
  • TNS alias
  • Oracle wallet (.zip)
  • Wallet password
Data source-OracleDB-Connection

Querying Oracle DB​

  1. Click on + Add button of the query manager at the bottom panel of the editor.
  2. Select the OracleDB Datasource added in previous step.
  3. Select the desired query mode.
  4. Click on the Preview button to preview the output or Click on the Run button to trigger the query.

Supported Operations​

Oracle DB supported operations

SQL mode​

SQL mode can be used to write raw SQL queries.

sql mode querying 
SELECT first_name, last_name, email
FROM employees
WHERE department_id = 15
ORDER BY last_name;

Parameterized Queries​

Parameterized queries pass values to the database separately from the SQL text, which prevents SQL injection. To use them:

  1. Use :parameter_name as placeholders in your SQL query where you want to insert parameters.
  2. In the SQL Parameters section below the query editor, add key-value pairs for each parameter.
  3. The keys should match the parameter names used in the query (without the colon).
  4. The values can be static values or dynamic values using the {{ }} notation.

Example:

SELECT first_name, last_name FROM employees WHERE department_id = :department_id

SQL Parameters:

  • Key: department_id
  • Value: {{ components.departmentSelect.value }}

GUI mode​

GUI mode can be used to query Oracle database without writing queries.

  1. Create a new query and select the Oracle DB Datasource.
  2. Select GUI mode from the dropdown.
  3. Choose the operation you want to perform.
  4. Optionally, select a Schema. If you don't select one, ToolJet lists the tables owned by the connected user.
  5. Fetch and select the Table name.
  6. Click on the Preview button to view the output or click on Run button to trigger the query.

List Rows​

Retrieve rows from a table with support for filtering, sorting, and pagination.

Required Parameter​

  • Table: Select the table from which rows need to be retrieved.

Optional Parameters​

  • Filter: Apply conditions to return only rows that match specific criteria.
  • Sort: Arrange the returned rows in ascending or descending order based on selected columns.
  • Aggregate: Apply aggregate functions such as count, sum, average, minimum, or maximum on selected columns.
  • Group by: Group rows that share the same values in selected columns into summarized results.
  • Limit: Restricts the number of rows returned in the result.
  • Offset: Skips a specified number of rows before starting to return results.
list rows GUI

Create Rows​

Insert a new row into the selected table by providing values for the required columns.

In the editor, ensure the Columns input is provided in string format.

Required Parameter​

  • Columns: Specifies the table columns and their corresponding values to be inserted when creating a new row.
Create rows GUI

Update Rows​

Modify existing row values in the selected table based on the specified conditions or identifiers.

In the editor, ensure the Columns input is provided in string format.

Required Parameter​

  • Columns: Specify the column names and values to be updated in the selected row(s).

Optional Parameters​

  • Filter: Apply conditions to identify which row(s) should be updated.
  • Allow this query to update multiple rows: When enabled, the query can modify multiple rows at once. When disabled, the query fails if the filter matches more than one row.
  • Consider query that updates zero records as success: When enabled, a query that matches no rows is still treated as successful. When disabled, the query fails if no row matches the filter.

Both toggles are off by default. ToolJet checks the number of matching rows before it runs the update, so when a toggle's condition is violated, no rows are changed.

Update Rows GUI

Delete Rows​

Remove one or more rows from the selected table that match the given conditions.

Optional Parameters​

  • Filter: Apply conditions to specify which row(s) should be deleted. Required unless you set a Limit.
  • Limit: Specify the maximum number of rows to delete.
  • Allow this query to delete multiple rows: When enabled, the query can delete multiple rows at once. When disabled, the query fails if more than one row would be deleted.
  • Consider query that deletes zero records as success: When enabled, a query that matches no rows is still treated as successful. When disabled, the query fails if no row matches the filter.

Both toggles are off by default. When a toggle's condition is violated, no rows are deleted.

Delete Rows GUI

Upsert Rows​

Insert a new row or update an existing row if a matching primary or unique key already exists.

In the editor, ensure the Columns input is provided in string format.

Required Parameters​

  • Primary key column(s): Specifies the column(s) used to identify whether a row already exists for updating or if a new row should be inserted.
  • Columns: Provide the column names and values to be inserted or updated.

Optional Parameters​

  • Allow this query to update multiple rows: When enabled, the query can modify multiple rows at once. When disabled, the query fails if more than one row matches the primary key.

The upsert form also shows Consider query that updates zero records as success, but it has no effect: when no row matches, ToolJet inserts a new row.

Upsert Rows GUI

Bulk Insert​

Inserts multiple rows into the table in a single operation using an array of records.

Required Parameters​

  • Table : Select the table into which multiple rows need to be inserted.
  • Records to insert: Provide the set of rows and corresponding column values to be inserted in a single operation.
Example Values
{{ [
  { id: 15, firstname: 'John' },
  { id: 16, firstname: 'Doe' },
  { id: 17, firstname: 'Alice' },
  { id: 18, firstname: 'Bob' },
  { id: 19, firstname: 'Charlie' },
  { id: 20, firstname: 'David' },
  { id: 21, firstname: 'Emma' },
  { id: 22, firstname: 'Frank' },
  { id: 23, firstname: 'Grace' },
  { id: 24, firstname: 'Henry' }
] }}
Bulk Insert GUI

Bulk Update using Primary Key​

Update multiple existing rows at once by matching records using their primary key values.

Required Parameters​

  • Primary key column: Specify the primary key column used to identify the rows that need to be updated.
  • Records to update: Provide multiple records with updated column values for the matching primary key rows.
Example Values
{{ [
  { id: 15, firstname: 'John Doe' },
  { id: 16, firstname: 'Alice Bob' },
  { id: 17, firstname: 'Emma Frank' },
  { id: 18, firstname: 'Grace Henry' }
] }}
Bulk Update GUI

Bulk Upsert using Primary Key​

Insert multiple new rows or update existing ones by matching rows using primary key values.

Required Parameters​

  • Primary key column(s): Specify the primary key column(s) used to determine whether each record should be updated or inserted.
  • Records to upsert: Provide multiple records that will be inserted as new rows or updated if matching primary key values already exist.

In this operation, if a row with the matching primary key exists, it is updated; otherwise, a new row is inserted.

Bulk Upsert GUI
tip

Query results can be transformed using transformations. Read our transformation documentation to see how: link