Oracle Database icon

Oracle Database

Interact with Oracle Database

Overview

This node allows you to interact with an Oracle Database by executing SQL statements. It supports two main operations under the "Database" resource:

  • Execute Query: Run SELECT queries to retrieve data from the database.
  • Execute Statement: Run DML (INSERT, UPDATE, DELETE) or DDL (CREATE, DROP, ALTER) statements and stored procedures that modify the database.

Common scenarios include fetching user data, updating records, creating tables, or running any SQL commands programmatically within an n8n workflow. For example, you could use this node to query customer information based on dynamic input parameters or execute a batch update on multiple rows.

Properties

Name Meaning
SQL Query The SQL statement to execute. For "Execute Query", typically a SELECT statement; for "Execute Statement", DML/DDL commands. Example: SELECT * FROM users WHERE id = :id
Parameters JSON object of bind parameters to safely inject values into the SQL query. Example: { "id": 123 }
Auto Commit (Only for Execute Statement) Boolean flag indicating whether to automatically commit transactions after execution. Default is true.
Connection Pool Options Configuration options for managing the Oracle connection pool:
- Pool Min: Minimum number of connections in the pool.
- Pool Max: Maximum number of connections in the pool.
- Queue Timeout (Ms): Time in milliseconds to wait for a connection from the pool before timing out.

Output

The node outputs an array of JSON objects representing the result of the executed SQL command:

  • For Execute Query, the output contains rows returned by the SELECT statement. The keys in each object correspond to column names, which can be formatted as uppercase, lowercase, or original case depending on the "Result Format" property.
  • For Execute Statement, the output contains objects with a single field affectedRows indicating how many rows were affected by the operation.

No binary data output is produced by this node.

Example output for a SELECT query:

[
  {
    "ID": 1,
    "NAME": "John Doe",
    "EMAIL": "john@example.com"
  }
]

Example output for an UPDATE statement:

[
  {
    "affectedRows": 3
  }
]

Dependencies

  • Requires an Oracle Database client library installed and configured properly on the host machine.
  • Needs an API key credential with Oracle database access configured in n8n.
  • Environment variables may be required to specify Oracle client library paths (ORACLE_CLIENT_LIB_PATH) and configuration directories (ORACLE_CLIENT_CONFIG_DIR).
  • Uses connection pooling to optimize database connections, configurable via node properties.

Troubleshooting

  • Error creating Oracle pool: Indicates issues initializing the connection pool, possibly due to incorrect credentials or misconfigured environment variables.
  • Environment variable ... is not set: The node expects certain environment variables for connection strings; ensure these are defined in your system.
  • Invalid query type for SELECT operation: This error occurs if you try to run non-SELECT statements using the "Execute Query" operation. Use "Execute Statement" instead.
  • Error executing query/statement: Could be caused by syntax errors in SQL, invalid parameters, or connectivity issues. Verify your SQL syntax and database availability.
  • Error closing connection/pool: Usually logged but does not stop execution; indicates cleanup issues with database connections.

To resolve most issues, verify credentials, environment variables, SQL syntax, and network connectivity to the Oracle database.

Links and References

Discussion