Overview
This node allows executing parameterized SQL queries against an Oracle Database. It supports running any valid SQL statement with dynamic parameters, including support for "IN" clauses with multiple values. The node can limit the number of rows returned and optionally include detailed metadata about the query result.
Common scenarios where this node is useful include:
- Querying data from Oracle databases with dynamic filters.
- Performing inserts, updates, or deletes using parameterized statements to avoid SQL injection.
- Fetching a limited subset of rows for reporting or processing.
- Returning both raw data rows and metadata for advanced use cases.
Example: Select products with IDs less than a given parameter, passing the parameter dynamically and limiting results to 100 rows.
Properties
| Name | Meaning |
|---|---|
| SQL Statement | The SQL query to execute. Supports parameter placeholders prefixed by colon (e.g., :param_name). |
| Parameters | A collection of named parameters to bind to the SQL query. Each parameter includes: - Name: Parameter name without colon. - Value: The value to bind. - Data Type: String or Number. - Parse for IN statement: If yes, treats the value as a comma-separated list for SQL IN clause expansion. |
| Include Metadata | If enabled, the output will include a metaData key containing the full query result object including metadata. Otherwise, only the rows are returned. |
| Row Limit | Limits the number of rows returned by appending FETCH FIRST X ROWS ONLY to the SQL query. Options: No limit, 100, 1,000, 10,000. |
Output
The node outputs an array of items, each with a json property:
- If Include Metadata is disabled (default), each item corresponds to one row from the query result, represented as a JSON object with column names as keys.
- If Include Metadata is enabled, a single item is returned with a
jsonobject containing the entire query result, including metadata such as column definitions and execution info.
No binary data output is produced by this node.
Dependencies
- Requires an Oracle Database connection configured via credentials that provide user, password, and connection string.
- Uses the official OracleDB Node.js driver (
oracledbpackage). - Requires n8n credentials setup for Oracle authentication.
- No additional environment variables are explicitly required beyond standard Oracle client configuration.
Troubleshooting
- Connection errors: Ensure Oracle credentials are correct and the database is reachable.
- SQL syntax errors: Verify the SQL statement syntax and parameter placeholders match the provided parameters.
- Parameter binding issues: Make sure parameter names in the SQL match exactly those defined in the Parameters property (without colon). For "IN" statements, ensure the value is a comma-separated string if "Parse for IN statement" is enabled.
- Row limit not working: Confirm the Row Limit property is set correctly; zero means no limit.
- Metadata not included: Enable the Include Metadata option to get full query metadata.
- Error closing connection: Usually logged but does not affect main execution; check Oracle client installation if persistent.