Skip to main content

Insert Table

Inserts data into an Oracle database table.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Connection Id - The ID of the database connection to use.
  • Transaction Id - The ID of the transaction to use (optional).
  • Table Name - The name of the database table to insert data into.
  • Table Data - The data to insert, provided as a table structure.

Options​

  • Replace - If true, existing rows that conflict are updated instead of inserted, using a MERGE statement.
  • Credentials - Database credentials, used instead of a Connection Id when you do not need a persistent connection.

Output​

  • None. The node produces no output variable. The message passes through unchanged, so you can chain another Insert Table or a Commit Transaction after it.

How It Works​

The Insert node inserts data into an Oracle database table by:

  1. Validating the provided Connection Id and Table Name
  2. Looking up the connection and transaction in the shared dictionaries
  3. Creating an INSERT command for each row in the table data
  4. Executing the INSERT command for each row
  5. Using a transaction if a Transaction Id is provided

Requirements​

  • An active database connection established with the Connect node
  • A valid table name that exists in the database
  • Table data in the correct format
  • Appropriate permissions to insert data into the specified table

Error Handling​

The node will return specific errors in the following cases:

ConditionWhat causes itHow to fix it
Empty or invalid Connection IdThe Connect node did not run, or the message variable holding the ID has a different nameWire a Connect node upstream, or supply Credentials on this node instead
Empty or invalid Table NameTable Name was left blank, or a Message variable resolved to emptySet Table Name as a literal, or check the upstream variable is populated
Connection Id not foundThe connection was closed by a Disconnect node earlier in the flow, or the robot restartedMove the Disconnect after the insert; connections do not survive a robot restart
Transaction Id not foundCommit Transaction already ran, or the ID came from a different branchKeep Start, Insert and Commit on the same branch

Usage Examples​

Example 1: Load a CSV into a Table​

- Connect -> conn_id
- CSV To Data Table (employees.csv) -> table
- Insert Table:
  - Connection Id: conn_id
  - Table Name: EMPLOYEES
  - Table Data: table
- Disconnect (conn_id)

In the SDK:

.then('a06926', 'Robomotion.Oracle.Insert', 'Load Employees', {
  inConnectionId: Message('conn_id'),
  inDatabaseTable: Custom('EMPLOYEES'),
  inTable: Message('table')
})

Example 2: Insert Without a Connect Node​

For a single insert, credentials on the node itself avoid the Connect and Disconnect pair:

.then('b17c34', 'Robomotion.Oracle.Insert', 'Log Run', {
  inDatabaseTable: Custom('RUN_LOG'),
  inTable: Message('table'),
  optCredentials: Credential({ vaultId: 'vault-uuid', itemId: 'item-uuid' })
})

Example 3: Update Existing Rows with Replace​

With Replace enabled the node issues a MERGE, so rows that already exist are updated rather than raising a unique-constraint error:

.then('c28d45', 'Robomotion.Oracle.Insert', 'Upsert Employees', {
  inConnectionId: Message('conn_id'),
  inDatabaseTable: Custom('EMPLOYEES'),
  inTable: Message('table'),
  optReplace: true
})

Example 4: Insert Two Tables in One Transaction​

Either both inserts land or neither does:

.then('d39e56', 'Robomotion.Oracle.Start', 'Begin', {
  inConnectionId: Message('conn_id'),
  outTransactionId: Message('trx_id')
})

.then('e40f67', 'Robomotion.Oracle.Insert', 'Insert Orders', {
  inConnectionId: Message('conn_id'),
  inTransactionId: Message('trx_id'),
  inDatabaseTable: Custom('ORDERS'),
  inTable: Message('orders_table')
})

.then('f51a78', 'Robomotion.Oracle.Insert', 'Insert Order Lines', {
  inConnectionId: Message('conn_id'),
  inTransactionId: Message('trx_id'),
  inDatabaseTable: Custom('ORDER_LINES'),
  inTable: Message('lines_table')
})

.then('a62b89', 'Robomotion.Oracle.Commit', 'Commit', {
  inTransactionId: Message('trx_id')
})

Usage Notes​

  • The Connection Id must be valid and correspond to an active connection
  • If a Transaction Id is provided, it must be valid and correspond to an active transaction
  • The Table Data should be structured as a table with rows and columns
  • When using the Replace option, existing rows with matching primary keys will be replaced
  • For large datasets, consider using batch operations for better performance

Tips​

  • This node's properties use the in prefix. They are inConnectionId, inTransactionId, inDatabaseTable and inTable. The otherwise identical PostgreSQL Insert Table node drops the prefix and uses connectionId, transactionId, databaseTable and table. Copying a node between the two packages needs the property names changed, not just the node type
  • Replace is a real boolean. Write optReplace: true, not Custom('true')
  • Replace matches on the table's primary key, and unlike the PostgreSQL node there is no property for choosing a different conflict target. If you need to match on something other than the primary key, use Execute Non Query with your own MERGE statement
  • Oracle folds unquoted identifiers to upper case, so a table created as employees is EMPLOYEES in the data dictionary. Passing the lower-case name usually still works; passing a name that was created quoted and mixed-case will not
  • The table shape is {columns: [...], rows: [{key: value}]} with row keys matching the column names, the same shape every table node in Robomotion uses
  • Credentials come from a Database vault item, which carries the host, port, SID or service name, user and password together, so none of those are separate node properties
  • The node runs one INSERT per row, so a very large table is many round trips. Where the load is big and regular, staging the data and calling a stored procedure through Execute Non Query is usually faster