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 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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

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

Drop a table:

{
  "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:

{
  "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:

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

Run a query with several named binds:

{
  "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.