<!-- BusyBot node reference — https://busybot.net/tools/oracle-sql/ -->

> Node: Oracle SQL (`oracle_sql`) · Action · v1
> Category: Data & Storage · Credentials: Oracle Database (`oracleDBApi`)
> Updated: 2026-08-16

# Oracle SQL

> Execute SQL operations against Oracle Database with named bind parameters, MERGE-based upsert, and three batching modes.

## Overview

Oracle Database tool for performing CRUD operations. Supports six operations: Execute (raw SQL with IN/OUT/INOUT bind variables), Select (query rows with WHERE/ORDER BY/LIMIT — uses `FETCH FIRST ROWS ONLY` on 12c+ or `ROWNUM` on older versions), Insert (add rows with three batching modes: single, independently, or transaction), Update (modify rows matched by column(s), with the same three batching modes), Upsert (`MERGE INTO ... USING DUAL`, the Oracle-native upsert), and Delete Table (DELETE with WHERE, TRUNCATE, or DROP with an ORA-00942 guard). All SQL identifiers are double-quoted and all comparison values are bound.

**Category:** Data & Storage  
**Tool Name:** `oracle_sql`  
**Version:** 1

**Appearance:** Icon: `lucide-Database` | Color: `#F80000`

## Node Type

**Action** — processes input items and produces output

## Input / Output

| Direction | Port(s) |
|-----------|--------|
| Input | `Input` |
| Output | `Output`, `Error` |

## Credentials

This tool requires **Oracle Database** credentials.
See the [Credentials Guide](https://busybot.net/credentials/oracle-dbapi/) for setup instructions.

### Operations

| Operation | Value | Description |
|-----------|-------|-------------|
| Delete Table | `deleteTable` | Delete rows, truncate, or drop a table |
| Execute | `execute` | Execute an arbitrary SQL statement or PL/SQL block with named bind parameters |
| Insert | `insert` | Insert one or more rows into a table |
| Select | `select` | Select rows from a table |
| Update | `update` | Update existing rows matched by column(s) |
| Upsert | `upsert` | Insert a row or update it if it already exists (Oracle MERGE INTO ... USING DUAL) |

### Parameters

#### Delete Table (`deleteTable`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Schema | `string` | No | — | Oracle schema (owner) name. Leave empty to use the connected user's default schema. |
| Table | `string` | Yes | — | The table to operate on. |
| Command | `options` | No | `delete` | Type of delete operation to perform. |
| | | | | Options: `delete` (DELETE FROM table, with optional WHERE), `truncate` (removes all rows instantly without logging), `drop` (removes the table entirely; dropping a table that does not exist still succeeds) |
| Filter Rows (WHERE) (`deleteWhere`) | `fixedCollection` | No | `{}` | WHERE conditions for the DELETE command. If none are set, all rows are deleted. _(shown when Command is `delete`)_ |
| — Column | `string` | No | — | Column name to filter on. |
| — Operator | `options` | No | `equal` | Comparison operator. |
| | | | | Options: `equal`, `!=`, `LIKE`, `>`, `<`, `>=`, `<=`, `IS NULL`, `IS NOT NULL` |
| — Value | `string` | No | — | _(hidden when Operator is `IS NULL`, `IS NOT NULL`)_ |
| Combine Conditions (`deleteCombineConditions`) | `options` | No | `AND` | How to combine the DELETE WHERE conditions. _(shown when Command is `delete`)_ |
| | | | | Options: `AND`, `OR` |

#### Execute (`execute`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Query | `string` | Yes | — | SQL query or PL/SQL block to execute. Use named bind variables (:name) for parameters. NEVER concatenate user input directly into SQL. |
| Auto Commit | `boolean` | No | `true` | Whether to commit the transaction automatically after execution. |
| Bind Parameters | `collection` | No | `{}` | Parameter bindings for the query using named bind variables. |
| — Parameters | `fixedCollection` | No | `{}` | Named bind parameters for the SQL query. |
| — — Name | `string` | No | — | Bind variable name (without the colon prefix, e.g. dept_id for :dept_id). |
| — — Direction | `options` | No | `IN` | Bind direction for the parameter. |
| | | | | Options: `IN`, `OUT`, `INOUT` |
| — — Data Type | `options` | No | `VARCHAR2` | Oracle data type for the bind variable. |
| | | | | Options: `VARCHAR2`, `NUMBER`, `DATE`, `CLOB`, `BLOB` |
| — — Value | `string` | No | — | Value to bind. Leave empty for OUT parameters. _(hidden when Direction is `OUT`)_ |

#### Insert (`insert`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Schema | `string` | No | — | Oracle schema (owner) name. Leave empty to use the connected user's default schema. |
| Table | `string` | Yes | — | The table to operate on. |
| Data Mode | `options` | No | `autoMapInputData` | How to specify the data for the operation. |
| | | | | Options: `autoMapInputData` (use all fields from the input item as column values), `defineBelow` (manually specify column-value pairs) |
| Column Values | `json` | No | `{}` | Column-value pairs as a JSON object (used when Data Mode is "Define Below"). _(shown when Data Mode is `defineBelow`)_ |
| Statement Batching | `options` | No | `single` | How to batch statements for insert/update/upsert operations. |
| | | | | Options: `single` (all items in one executeMany call — fastest, reports batchErrors without halting), `independently` (one execute per item — errors on one item do not affect others), `transaction` (all items wrapped in a transaction — rolls back everything on any error) |
| Output Columns (RETURNING INTO) | `string` | No | — | Comma-separated columns to return after insert/update using RETURNING INTO, or leave empty to not use RETURNING. |

#### Select (`select`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Schema | `string` | No | — | Oracle schema (owner) name. Leave empty to use the connected user's default schema. |
| Table | `string` | Yes | — | The table to operate on. |
| Return All | `boolean` | No | `true` | Whether to return all results or only up to a given limit. |
| Limit | `number` | No | `50` | Max number of rows to return. On Oracle 12c+, uses FETCH FIRST n ROWS ONLY. On older versions, uses ROWNUM wrapping. _(shown when Return All is `false`)_ |
| Filter Rows (WHERE) (`where`) | `fixedCollection` | No | `{}` | WHERE filter conditions. If none are set, all rows are returned. |
| — Column | `string` | No | — | Column name to filter on. |
| — Operator | `options` | No | `equal` | Comparison operator. |
| | | | | Options: `equal`, `!=`, `LIKE`, `>`, `<`, `>=`, `<=`, `IS NULL`, `IS NOT NULL` |
| — Value | `string` | No | — | Value to compare against. Not used for IS NULL / IS NOT NULL. _(hidden when Operator is `IS NULL`, `IS NOT NULL`)_ |
| Combine Conditions (`combineConditions`) | `options` | No | `AND` | How to combine the WHERE conditions. |
| | | | | Options: `AND` (all conditions must be true), `OR` (at least one condition must be true) |
| Sort | `fixedCollection` | No | `{}` | ORDER BY rules. |
| — Column | `string` | No | — | Column to sort by. |
| — Direction | `options` | No | `ASC` | Sort direction. |
| | | | | Options: `ASC`, `DESC` |

#### Update (`update`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Schema | `string` | No | — | Oracle schema (owner) name. Leave empty to use the connected user's default schema. |
| Table | `string` | Yes | — | The table to operate on. |
| Data Mode | `options` | No | `autoMapInputData` | How to specify the data for the operation. |
| | | | | Options: `autoMapInputData` (use all fields from the input item as column values), `defineBelow` (manually specify column-value pairs) |
| Column Values | `json` | No | `{}` | Column-value pairs as a JSON object (used when Data Mode is "Define Below"). _(shown when Data Mode is `defineBelow`)_ |
| Matching Columns | `string` | Yes | `id` | Comma-separated column name(s) to match rows on for update/upsert operations. |
| Statement Batching | `options` | No | `single` | How to batch statements for insert/update/upsert operations. |
| | | | | Options: `single` (all items in one executeMany call — fastest, reports batchErrors without halting), `independently` (one execute per item — errors on one item do not affect others), `transaction` (all items wrapped in a transaction — rolls back everything on any error) |
| Output Columns (RETURNING INTO) | `string` | No | — | Comma-separated columns to return after insert/update using RETURNING INTO, or leave empty to not use RETURNING. |

#### Upsert (`upsert`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Schema | `string` | No | — | Oracle schema (owner) name. Leave empty to use the connected user's default schema. |
| Table | `string` | Yes | — | The table to operate on. |
| Data Mode | `options` | No | `autoMapInputData` | How to specify the data for the operation. |
| | | | | Options: `autoMapInputData` (use all fields from the input item as column values), `defineBelow` (manually specify column-value pairs) |
| Column Values | `json` | No | `{}` | Column-value pairs as a JSON object (used when Data Mode is "Define Below"). _(shown when Data Mode is `defineBelow`)_ |
| Matching Columns | `string` | Yes | `id` | Comma-separated column name(s) to match rows on for update/upsert operations. |
| Statement Batching | `options` | No | `single` | How to batch statements for insert/update/upsert operations. |
| | | | | Options: `single` (all items in one executeMany call — fastest, reports batchErrors without halting), `independently` (one execute per item — errors on one item do not affect others), `transaction` (all items wrapped in a transaction — rolls back everything on any error) |

#### All Operations

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Output Large Numbers As Strings | `boolean` | No | `true` | When true, numbers exceeding JavaScript's safe integer range are returned as strings to preserve precision. |
| Max Concurrency | `number` | No | `5` | Maximum number of items to process concurrently. Oracle connections are heavier than HTTP; keep this low. |

## Output Data

Results replace the item. Each row a statement returns becomes its own output item and the row's columns *are* that item's JSON — the input item's JSON does not pass through, and binary data on the input item is not carried forward. A select that returns 200 rows turns one input item into 200 output items.

For Insert, Update and Upsert, Statement Batching decides how many items come out:

- `single` and `transaction` collect every input item and run one batch, so the whole node run produces **one** output item no matter how many items went in.
- `independently` runs one statement per item and produces one output item per input item. A row that Oracle rejects in this mode comes back on the main **Output** with `success: false` and an `error` message — it is not routed to the Error port, so check the flag downstream.

| Operation | Output items |
|-----------|--------------|
| `execute` | One item per returned row. A statement with OUT or IN/OUT binds instead returns a single item holding the out-bind values keyed by bind name, plus `rowsAffected`. A statement that returns neither rows nor out-binds — an INSERT, a DDL statement, a PL/SQL block — produces one item: `{ "success": true, "rowsAffected": 3 }` |
| `select` | One item per matching row. No matches produces one item carrying the input item's JSON plus `_queryResult: []` |
| `insert`, `update`, `upsert` — `single` | One item: `{ "success": true, "rowsAffected": 12 }`, plus `batchErrorCount` and `batchErrors` (each with `offset` and `message`) when individual rows in the batch were rejected |
| `insert`, `update`, `upsert` — `transaction` | One item: `{ "success": true, "rowsAffected": 12 }`, where the count is the number of items sent |
| `insert`, `update`, `upsert` — `independently` | One item per input item: `{ "success": true, "rowsAffected": 1, "itemIndex": 0 }`, or `{ "success": false, "error": "…", "itemIndex": 0 }` for a row Oracle rejected |
| `deleteTable` | One item: `{ "success": true, "rowsAffected": 12, "command": "DELETE" }`, `{ "success": true, "command": "TRUNCATE" }`, or `{ "success": true, "command": "DROP" }` |

Reference the result downstream by expression, e.g. `{{ $json.rowsAffected }}` or, for a select, the column name itself — `{{ $json.EMPLOYEE_ID }}`.

## Usage Examples

- SELECT rows from an Oracle table with WHERE filters and ORDER BY
- INSERT rows into an Oracle table using executeMany batching
- UPDATE existing rows matched by employee ID column
- UPSERT data using Oracle MERGE INTO ... USING DUAL pattern
- Execute a PL/SQL block with IN/OUT bind parameters
- TRUNCATE or DROP an Oracle table
- Delete rows matching specific conditions

## Example Configuration

Read rows with two WHERE conditions, a row limit and a two-level sort:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "select",
    "schema": "HR",
    "table": "EMPLOYEES",
    "returnAll": false,
    "limit": 10,
    "where": {
      "values": [
        {
          "column": "DEPARTMENT_ID",
          "condition": "equal",
          "value": "50"
        },
        {
          "column": "SALARY",
          "condition": ">=",
          "value": "3000"
        }
      ]
    },
    "combineConditions": "AND",
    "sort": {
      "values": [
        {
          "column": "LAST_NAME",
          "direction": "ASC"
        },
        {
          "column": "SALARY",
          "direction": "DESC"
        }
      ]
    }
  }
}
```

Insert every field of each input item as a row, in one batch:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "insert",
    "schema": "HR",
    "table": "EMPLOYEES",
    "dataMode": "autoMapInputData",
    "stmtBatching": "single"
  }
}
```

Insert fixed column values instead of the input item's fields:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "insert",
    "schema": "HR",
    "table": "EMPLOYEES",
    "dataMode": "defineBelow",
    "columns": {
      "FIRST_NAME": "John",
      "LAST_NAME": "Doe",
      "EMAIL": "john.doe@company.com",
      "DEPARTMENT_ID": 50,
      "SALARY": 4500
    },
    "stmtBatching": "independently"
  }
}
```

Update rows matched by a key column — the matching column has to be part of the data, so include it in Column Values:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "update",
    "schema": "HR",
    "table": "EMPLOYEES",
    "dataMode": "defineBelow",
    "columns": {
      "EMPLOYEE_ID": 145,
      "SALARY": 5000,
      "DEPARTMENT_ID": 60
    },
    "matchingColumns": "EMPLOYEE_ID",
    "stmtBatching": "transaction"
  }
}
```

Insert or update in a single statement, matched on a unique column:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "upsert",
    "schema": "HR",
    "table": "EMPLOYEES",
    "dataMode": "autoMapInputData",
    "matchingColumns": "EMAIL",
    "stmtBatching": "single"
  }
}
```

Run a PL/SQL block with named bind parameters:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "execute",
    "query": "BEGIN UPDATE employees SET salary = salary * :raise_factor WHERE department_id = :dept_id; COMMIT; END;",
    "autoCommit": true,
    "options": {
      "params": {
        "values": [
          {
            "name": "raise_factor",
            "direction": "IN",
            "datatype": "NUMBER",
            "value": "1.1"
          },
          {
            "name": "dept_id",
            "direction": "IN",
            "datatype": "NUMBER",
            "value": "50"
          }
        ]
      }
    }
  }
}
```

Delete only the rows that match a set of conditions:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "deleteTable",
    "schema": "HR",
    "table": "TEMP_DATA",
    "deleteCommand": "delete",
    "deleteWhere": {
      "values": [
        {
          "column": "CREATED_DATE",
          "condition": "<",
          "value": "2023-01-01"
        },
        {
          "column": "STATUS",
          "condition": "equal",
          "value": "PROCESSED"
        }
      ]
    },
    "deleteCombineConditions": "AND"
  }
}
```

Empty a table:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "deleteTable",
    "schema": "HR",
    "table": "TEMP_LOG",
    "deleteCommand": "truncate"
  }
}
```

Drop a table:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "deleteTable",
    "schema": "HR",
    "table": "OLD_TABLE",
    "deleteCommand": "drop"
  }
}
```

Page through a table by taking a fixed number of rows in a defined order:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "select",
    "table": "ORDERS",
    "returnAll": false,
    "limit": 50,
    "sort": {
      "values": [
        {
          "column": "ORDER_DATE",
          "direction": "DESC"
        }
      ]
    }
  }
}
```

Wrap a batch of updates in a transaction so any failure rolls all of them back:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "update",
    "table": "INVENTORY",
    "dataMode": "autoMapInputData",
    "matchingColumns": "PRODUCT_ID",
    "stmtBatching": "transaction"
  }
}
```

Run a query with several named binds:

```json
{
  "type": "oracle_sql",
  "parameters": {
    "operation": "execute",
    "query": "SELECT * FROM orders WHERE order_date BETWEEN :start_date AND :end_date AND customer_id = :cust_id",
    "options": {
      "params": {
        "values": [
          {
            "name": "start_date",
            "direction": "IN",
            "datatype": "DATE",
            "value": "2023-01-01"
          },
          {
            "name": "end_date",
            "direction": "IN",
            "datatype": "DATE",
            "value": "2023-12-31"
          },
          {
            "name": "cust_id",
            "direction": "IN",
            "datatype": "NUMBER",
            "value": "12345"
          }
        ]
      }
    }
  }
}
```

### Error Handling

| Mode | Behavior |
|------|----------|
| **stop** | Halts workflow on first error |
| **continue** | Skips failed items, passes successful ones through |
| **errorPort** | Routes failed items to Error output port |

## Tips

Execute SQL operations (SELECT, INSERT, UPDATE, UPSERT/MERGE, DELETE/TRUNCATE/DROP, raw SQL) against Oracle Database with named bind parameters and three batching modes.

### Behavior notes

- **Batching sets your item count.** `single` and `transaction` produce one summary item for the whole run, `independently` produces one item per input item. Pick `independently` when downstream nodes need a result per record, `single` when you are loading volume, and `transaction` when partial success is unacceptable.
- **Auto-map takes its column list from the first item.** Every item in the batch is bound against that same set of columns, so if later items carry extra fields those fields are dropped, and if they are missing a column it binds as null. Normalize the shape upstream (an Edit Fields node) before a large auto-mapped load.
- **Matching columns must be part of the data.** For Upsert, a matching column that is missing from the row fails the item outright. For Update it binds as null instead, and a null never matches — the statement succeeds and changes nothing. With Data Mode set to Define Below, list the matching column in Column Values as well.
- **Values are bound; names are not.** Comparison values, column values and bind parameters all travel as bind variables. Schema, table and column names are written into the statement as quoted identifiers, and the WHERE operator goes in as the keyword you picked — keep untrusted input out of those fields and choose operators from the list.
- **Drop is forgiving.** Dropping a table that does not exist reports success rather than failing, so a Delete Table / `drop` step is safe to run in a cleanup path that may already have run.
- **Limit adapts to the server.** On Oracle 12c and later the row cap becomes `FETCH FIRST n ROWS ONLY`; on older releases the query is wrapped in a `ROWNUM` filter instead.
- **Large numbers.** Output Large Numbers As Strings applies to the rows a Select returns, keeping values beyond JavaScript's safe integer range intact as strings.
- **Keep concurrency low.** Execute, Select, Delete Table and the `independently` batching mode work through the items in parallel, so Max Concurrency is how many statements hit the database at once. Oracle work is heavier than an HTTP call — the default of 5 is a sensible ceiling.