Oracle Database icon

Oracle Database

Interact with Oracle Database
Join our community

Actions4

Overview

This node enables interaction with an Oracle Database by executing SQL statements. It supports two main operations under the "Database" resource:

  • Execute Query: Runs SELECT queries to retrieve data.
  • Execute Statement: Executes DML (INSERT, UPDATE, DELETE), DDL (CREATE, DROP, ALTER), or stored procedure calls.

The node manages connection pooling for efficient database access and supports parameter binding in queries/statements. It also allows customization of transaction commit behavior and output formatting.

Common scenarios:

  • Fetching user records or reports via SELECT queries.
  • Running database updates, inserts, or schema changes programmatically.
  • Executing stored procedures with input/output parameters.
  • Integrating Oracle DB operations into automated workflows.

Practical example:

  • Use "Execute Query" to fetch all orders for a customer by passing a parameterized SELECT statement.
  • Use "Execute Statement" to insert a new record into a table with auto-commit enabled.

Properties

Name Meaning
Resource Fixed to "Database" for this node.
Operation Choose between "Execute Query" (SELECT) or "Execute Statement" (DML/DDL/procedures).
SQL Query (query) The SQL statement to execute. Supports parameter placeholders like :id.
Parameters (parameters) JSON object defining bind parameters for the query/statement.
Auto Commit (autoCommit) (Only for Execute Statement) Whether to automatically commit transactions after execution. Default: true.
Include Other Input Fields (includeOtherInputFields) Whether to include all original input fields in the output along with query results or execution info. Default: false.
Result Format (format) (Only for Execute Query) Format of result keys: Uppercase, Lowercase, or Original case.
Connection Pool Options (poolOptions) Collection of options to configure the connection pool:
- Pool Min: Minimum connections (default 2)
- Pool Max: Maximum connections (default 20)
- Queue Timeout (Ms): Wait time for connection when pool is busy (default 60000 ms)

Output

  • For Execute Query:

    • Outputs an array of JSON objects representing rows returned by the SELECT query.
    • Each row's keys can be formatted as uppercase, lowercase, or original case based on the "Result Format" property.
    • If "Include Other Input Fields" is enabled, each output item merges the original input JSON with the query result row.

  • For Execute Statement:

    • Outputs JSON objects containing execution metadata such as:
      • affectedRows: Number of rows affected by the statement.
      • Any output bind variables if used.
    • If "Include Other Input Fields" is enabled, the output merges these metadata with the original input JSON.

  • Binary data output is not supported by this node.

Dependencies

  • Requires an Oracle Database accessible with valid credentials.
  • Needs an API key credential configured in n8n for authentication to the Oracle DB.
  • Uses the official OracleDB Node.js driver internally.
  • Optionally requires environment variables for connection strings if using that mode.
  • Optional Oracle client libraries may be needed depending on the connection mode (thin vs thick).

Troubleshooting

  • Connection pool errors:

    • Error message about connection timeout or all connections busy suggests increasing the maximum pool size or optimizing queries.
    • Ensure environment variables for connection strings are set if using that mode.

  • Invalid JSON parameters:

    • If the "Parameters" JSON is malformed, the node throws an error indicating invalid JSON. Fix the JSON syntax accordingly.

  • Unsupported query type for Execute Query:

    • Attempting to run INSERT/UPDATE/DELETE with the "Execute Query" operation will cause an error. Use "Execute Statement" instead.

  • Oracle client initialization errors:

    • If Oracle client libraries are missing or misconfigured, initialization may fail. Verify environment variables pointing to Oracle client library paths.

  • Output bind variable errors:

    • Using output bind variables requires specifying valid OracleDB types. Invalid types cause errors.

Links and References

Discussion