Oracle Database icon

Oracle Database

Interact with Oracle Database

Actions4

Database → Execute Statement

AI-generated

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