Actions4
- Database Actions
Overview
This node allows executing SQL queries and statements against an Oracle Database. It supports both SELECT queries (to retrieve data) and DML/DDL statements or stored procedures (to modify data or database structure). The node manages connection pooling for efficient reuse of database connections, supports parameter binding in JSON format, and can optionally include all input fields in the output.
Common scenarios:
- Retrieving user or transactional data with a SELECT query.
- Executing INSERT, UPDATE, DELETE, CREATE, DROP, or ALTER statements to modify the database.
- Running stored procedures with input/output parameters.
- Using parameterized queries to prevent SQL injection.
- Managing transactions with optional auto-commit control.
Practical examples:
- Fetching customer details by ID using a parameterized SELECT query.
- Updating order status with an UPDATE statement and committing automatically.
- Creating a new table or altering schema via DDL statements.
- Calling a stored procedure that returns output parameters.
Properties
| Name | Meaning |
|---|---|
| SQL Query | The SQL statement to execute. For SELECT queries or DML/DDL statements depending on operation. |
| Parameters | Bind parameters for the SQL query in JSON format. |
| Auto Commit | Whether to automatically commit transactions after executing statements (only for Execute Statement). |
| Include Other Input Fields | Whether to include all original input fields alongside the query result in the output. |
| Connection Pool Options | Options to configure the connection pool: - Pool Min: Minimum number of connections - Pool Max: Maximum number of connections - Queue Timeout (Ms): Time to wait for a connection when pool is busy |
Additional property for SELECT queries only:
| Result Format | Format of the query result keys:
- Uppercase
- Lowercase
- Original (no change) |
Output
The node outputs an array of JSON objects representing the results of the executed query or statement:
- For SELECT queries, each object corresponds to a row returned by the query. The keys are column names formatted according to the selected "Result Format" option (uppercase, lowercase, or original).
- For DML/DDL statements or procedures, the output contains metadata such as the number of affected rows (
affectedRows) and any output bind parameters returned by the statement. - If "Include Other Input Fields" is enabled, the output JSON objects merge 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 containing connection details and authentication.
- Uses the official OracleDB Node.js driver library.
- Requires environment variables if using connection strings from environment (e.g.,
ORACLE_CLIENT_LIB_PATHfor Oracle client libraries). - Needs proper configuration of Oracle client libraries if not using thin mode.
- 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.
- Connection timeout (NJS-040): All connections in the pool are busy. Increase the maximum pool size or optimize queries to reduce connection hold time.
- Invalid JSON parameters: The "Parameters" field must contain valid JSON. Fix syntax errors or use the provided fix mechanism by correcting missing values.
- Invalid query type for SELECT operation: Attempting to run non-SELECT queries under the "Execute Query" operation will throw an error. Use "Execute Statement" for DML/DDL commands.
- Environment variable not set: When using connection strings from environment variables, ensure the specified environment variable exists and is accessible.
- Oracle client initialization errors: Verify Oracle client libraries are installed and paths configured correctly if not using thin mode.