Actions4
- Database Actions
Overview
This node allows executing SQL queries and statements against an Oracle Database using connection pooling for efficient resource management. It supports two main operations under the "Database" resource:
- Execute Query: Run SELECT queries to retrieve data.
- Execute Statement: Run DML (INSERT, UPDATE, DELETE), DDL (CREATE, DROP, ALTER), or stored procedure calls.
The node is useful in scenarios where you need to integrate Oracle database operations into your workflows, such as fetching user data, updating records, or managing database schema changes programmatically.
Practical examples:
- Fetching customer details by ID with a parameterized SELECT query.
- Inserting new records into a table using an INSERT statement.
- Running maintenance scripts like creating or dropping tables.
Properties
| Name | Meaning |
|---|---|
| SQL Query | The SQL statement to execute. For "Execute Query", this should be a SELECT statement. For "Execute Statement", it can be any valid DML/DDL or procedure call. Supports bind parameters using named placeholders (e.g., :id). |
| Parameters | JSON object defining bind parameters referenced in the SQL query. For example, { "id": 123 } binds the value 123 to :id. |
| Include Other Input Fields | Boolean flag indicating whether to include all input fields from the incoming item in the output along with the query results or execution metadata. |
| Result Format | (Only for Execute Query) Defines how column names in the result are formatted: Uppercase, Lowercase, or Original (no change). |
| Connection Pool Options | Collection of options to configure the Oracle connection pool: - Pool Min: Minimum number of connections maintained. - Pool Max: Maximum number of connections. - Queue Timeout (Ms): Wait time when pool is busy. |
Output
- For Execute Query, the output is an array of JSON objects representing rows returned by the SELECT query. Column names are formatted according to the selected "Result Format". If no rows are returned, an empty object is output.
- For Execute Statement, the output contains metadata about the execution, such as the number of affected rows (
affectedRows) and any output bind variables if used. - If "Include Other Input Fields" is enabled, the output merges the original input fields with the query results or execution metadata.
- The node does not output binary data.
Dependencies
- Requires an Oracle Database accessible via credentials that provide user, password, host, port, and service name or connection string.
- Uses an Oracle client library which may require environment variables to specify Oracle client library paths if not running in thin mode.
- Requires configuration of an API key credential for authentication to the Oracle database.
- Environment variables may be needed for connection strings if configured that way.
- Connection pooling is managed internally with configurable pool size and timeout options.
Troubleshooting
- Connection pool not initialized: Ensure credentials are correctly set and the node has successfully created a connection pool before executing queries.
- Environment variable not set: If using connection string from environment variables, verify the specified environment variable exists and contains a valid connection string.
- Connection timeout (NJS-040): Indicates all connections in the pool are busy. Consider increasing the maximum pool size or optimizing queries to release connections faster.
- Invalid JSON parameters: The "Parameters" field must contain valid JSON. Fix syntax errors or use the node's JSON editor.
- Invalid query type for SELECT operation: When using "Execute Query", only SELECT statements are allowed. Use "Execute Statement" for other SQL commands.
- Error closing connection: Usually logged but does not stop execution; ensure proper network stability and Oracle client installation.
- Oracle client initialization errors: Check that Oracle client libraries are installed and environment variables for their paths are correctly set.