This guide covers best practices for securely executing operations with these custom Node-RED nodes.
For reliable message processing where failed operations return messages to the queue, use the begin/end transaction pattern with dedicated commit and rollback paths:
begin transaction → dequeue → (processing) → end transaction (commit)
↓ (error)
catch → end transaction (rollback)
Messages stay locked on the queue until end-transaction commits. If the flow fails, the catch node routes to a rollback end-transaction and messages return to the queue automatically.
If your flow includes enqueue, keep it inside the same begin/end transaction path so the enqueue is only finalized on commit and is undone on rollback.
Connection timeout: Set a timeout on begin-transaction (e.g. 300 seconds) to auto-rollback stalled flows and prevent connection leaks.
When timeout is enabled, a reused transaction refreshes the timeout window to avoid stale-timer expiry in looping flows.
If end-transaction receives a timed-out transaction, it raises Transaction timed out through Catch instead of silently passing success.
If the same message hits end-transaction twice, the second pass is ignored with status already ended to prevent duplicate commit/rollback attempts.
Standalone mode: Dequeue can run without transaction nodes for simple use cases, but messages are auto-committed on dequeue and cannot be rolled back on downstream failure.
In Continuous mode, enable retry controls to survive transient DB outages without redeploying the flow. The node applies retry controls to DB/dequeue errors. For multi-consumer queues, configure Subscriber with the AQ consumer name; ORA-25231 means the consumer name is missing and stops immediately.
Dequeue mode: Use Remove (default) for normal message consumption. Use Browse for monitoring queue contents without consuming. Use Locked only when you need to inspect before deciding to remove.
- Always use bind variables instead of string concatenation to prevent SQL injection
- Use least-privileged database users
- Encrypt sensitive data
- Validate inputs before executing
autoCommit behavior: The SQL node uses autoCommit: false. SELECT queries work as expected. Standalone DML statements (INSERT, UPDATE, DELETE) are not committed and will roll back when the standalone connection closes; for standalone DML, use a PL/SQL block with an explicit COMMIT. When the flow is inside begin/end transaction nodes, SQL uses msg.transaction.connection and the end-transaction node commits or rolls back the work.
Dynamic SQL: When SQL Source is set to msg.sql, the query is read from the incoming message and executed as-is, with the configured DB user's privileges. The editor's single-statement guard does not apply to msg.sql (so it can run anonymous PL/SQL blocks). Only feed msg.sql from trusted flow logic — never from unvalidated HTTP, MQTT, or other external input — and pair it with least-privileged DB users.
Bind parity checks: The SQL node fails fast when SQL placeholders and bind values do not match, with status binds mismatch before DB execute.
Use Mapped fields when the flow should explicitly select, rename, type, or default individual Fusion attributes. Use Entire msg.payload when an upstream node already produces the complete Fusion request object. The modes are mutually exclusive: Mapped fields never falls back to the injected payload, and direct mode retains but ignores the mapping table so switching back does not lose its configuration.
Mapped fields requires at least one usable mapping for create, update, and other body-producing operations. An empty mapping table fails before OAuth token acquisition even when msg.payload contains an object. Parameterless GET and DELETE requests remain valid without mappings; use Entire msg.payload when a GET needs query parameters.
Direct payload mode validates and deep-copies a plain JSON-compatible object before OAuth token acquisition. Do not use it as a pass-through for arbitrary untrusted input; validate externally supplied data against the target Fusion API contract before it reaches the node. Node-specific mode defaults and required wrappers are added only to the copy.
All SCM nodes that use payload mappings support structured mapping rows with typed source options:
| Source | Reads from | Value example |
|---|---|---|
| dequeued data | msg.dequeued.<value> |
AssetNumber (prefix added automatically) |
| msg property | msg.<value> |
payload.someField |
| static text | Literal string | NODE_RED |
| static number | Numeric literal | 1 |
| static boolean | Boolean literal | Dropdown value: true or false |
| static JSON | Parsed JSON literal | ["SN1","SN2"] — array/object for nested fields such as serials |
| current timestamp | Runtime clock | Generated ISO timestamp |
Clicking Done saves structured mapping rows. Reopening a Fusion SCM, SQL, OCI Logging, or OCI Log Analytics node restores its saved mapping configuration.
Authentication: The IoT device nodes (iot-config, iot-telemetry, iot-subscribe) use MQTT device credentials — username/password or certificates. The cloud-side nodes (iot-send-command, iot-get-content, iot-update-relationship) use OCI user credentials via oci-config. These are separate auth contexts.
Persistent sessions: iot-config defaults to clean: false so the IoT Platform retains messages while the device is briefly offline. Keep this default for command/session reliability unless you explicitly need clean-session behavior.
Subscription patterns: In command subscriptions, use valid MQTT wildcards only (+ for one full segment, # only as the final segment). Invalid patterns are rejected.
Command responses: The iot-subscribe node does not auto-acknowledge. To send a response after processing a command-topic message, publish explicitly using a separate iot-telemetry or mqtt out node on whatever response topic your protocol requires.
Client ID uniqueness: Only one MQTT connection per Client ID is allowed. If you use both iot-config and built-in MQTT nodes with the same Client ID, they will disconnect each other. Use different Client IDs or use one or the other.
Authentication: ORDS nodes use OAuth client credentials through ords-config. This is separate from oci-config OCI signing and db-connection direct database login.
Base URL ownership: Keep the ORDS host/base path in ords-config. Request and polling nodes accept relative paths only so flows do not accidentally mix environments.
Request defaults: oci-ords-request defaults to Custom, so use it as a general ORDS request node. Select the IoT Data API presets only when those shortcuts match the endpoint you need.
Polling volume: Use oci-ords-poll for command status/response checks. When it follows iot-send-command, leave Record ID empty so the poll node reads msg.recordId from the command response. Max Concurrent Polls and Max Queued Polls live on ords-config as shared safety limits for all poll nodes using that profile; regular ORDS request nodes ignore them. Tune interval and timeout on each poll node for the workflow. Prefer shorter timeouts and bounded queues when many commands can be sent in bursts.
Retries: Treat oci-ords-request as a one-shot HTTP request. Use polling only when the workflow is genuinely asynchronous, such as waiting for command delivery status or response data.
Topic OCID vs dynamic routing: Hardcode the Topic OCID in the editor for fixed alerting targets. Leave it empty and set msg.topicOcid for dynamic routing (e.g. different severity levels to different topics).
Confirm subscriptions: Email subscriptions require clicking a confirmation link before they receive messages. Test with a simple inject → notification flow to verify delivery.
When using connection pooling on the db-connection config node:
| Setting | Suggested Value | Description |
|---|---|---|
| Pool Min | 2 | Keeps connections warm for fast response |
| Pool Max | 10 | Prevents exhausting database sessions |
| Pool Increment | 1 | Grows the pool gradually under load |
| Queue Timeout | 60000 (ms) | Fails fast if no connection is available within 60 seconds |
Adjust based on your workload and database session limits.
Transactions and pooling: begin-transaction borrows one connection and holds it until the matching end-transaction (or the transaction timeout) runs. With pooling enabled, that connection is unavailable to the pool for the whole begin→end span, so flows that wait on slow or external steps between begin and end can exhaust a small pool — size Pool Max and Queue Timeout for the number of transactions you expect to run concurrently.