@inteli.city/node-red-contrib-oracledb-async 1.0.1

Node-RED node for async Oracle DB query execution with explicit queue, concurrency control, and wallet-based Oracle Cloud connectivity

npm install @inteli.city/node-red-contrib-oracledb-async

async.oracle — Node-RED Oracle DB Async Node

A Node-RED node for executing Oracle SQL statements with explicit queue control, configurable concurrency, and first-class support for Oracle Cloud wallet-based connectivity.

No Oracle Instant Client required. The node uses node-oracledb in Thin mode only.

Index

1. Overview
2. Quick Start
3. Installation
4. Configuration
5. Usage
6. Bind Parameters
7. Nunjucks Templating
8. Concurrency Model
9. Message Flow
10. Status Indicator
11. Cancel Button
12. Error Handling
13. Oracle Cloud Wallet Setup
14. Common Issues
15. Design Principles

1. Overview

async.oracle executes one Oracle SQL statement for each incoming message.

It is designed to:

• control database concurrency with a single explicit knob
• avoid hidden queues or surprising buffering behaviour
• provide predictable, sequential-or-parallel execution
• make Oracle Cloud wallet-based connectivity a first-class configuration path

The node operates exclusively in Thin mode — Oracle Instant Client is not required on the host.

2. Quick Start

For Oracle Cloud Autonomous Database with a wallet:

1. Download the wallet zip from the Oracle Cloud Console and unzip it to a local directory.
2. In Node-RED, add a config.oracle config node and fill in:
   • Connect String — a TNS alias from the wallet's tnsnames.ora, e.g. mydb_medium
   • Username — your Oracle database user, e.g. ADMIN
   • Password — your Oracle database password
   • Wallet Directory — the full path to the unzipped wallet folder
   • Wallet Password — only if your wallet uses ewallet.p12 encryption (leave blank otherwise)
3. Drop an async.oracle node onto your flow and select the config node.
4. Send any message into the node with SQL Query set to SELECT 1 FROM DUAL.
5. Check the debug output — a row result confirms a successful connection.

3. Installation

npm install @inteli.city/node-red-contrib-oracledb-async

Or place the package in your Node-RED node_modules directory and restart Node-RED.

4. Configuration

Oracle config node (config.oracle)

All connection and security settings live here. Runtime nodes reference this config node — they never duplicate these fields.

Connection

Authentication

Wallet (optional)

The wallet provides mTLS authentication and TNS alias resolution. It does not replace database credentials (Username/Password) and does not grant network access to the database endpoint.

Runtime node (async.oracle)

5. Usage

1. Create a config.oracle config node with your Oracle connection details.
2. Drop an async.oracle node onto your flow and select the config node.
3. Write your SQL statement in the editor.
4. Set msg.params upstream with any bind parameters the query needs.
5. Connect an input and an output.

On each incoming message the node executes the SQL statement and sets msg.payload to the result:

• SELECT → msg.payload is an array of row objects (column names as keys).
• DML (INSERT, UPDATE, DELETE) → msg.payload is an empty array and msg.rowsAffected is set.

6. Bind Parameters

Bind parameters are the recommended way to pass dynamic values into SQL. They keep the SQL text and data separate, prevent SQL injection, and allow Oracle to reuse parsed execution plans.

Use :name placeholders in the SQL and provide values via msg.params:

SELECT * FROM orders
WHERE customer_id = :cid AND status = :status

msg.params = { cid: 42, status: "SHIPPED" };

Positional binds are also supported using :1, :2, … placeholders with an array:

SELECT * FROM products WHERE id = :1

msg.params = [99];

If the SQL has no bind variables, omit msg.params or set it to {}.

7. Nunjucks Templating

Switch the Syntax selector to Nunjucks to render the SQL as a template before execution. The full msg object is the template context.

Use this only for structural parts of the query that bind variables cannot cover — table names, dynamic column lists, conditional clauses:

SELECT * FROM {{ payload.tableName }}
WHERE region = :region
ORDER BY {{ payload.sortColumn }}

Warning: Nunjucks interpolation embeds values directly into the SQL string, bypassing Oracle's bind-variable safety guarantees. Do not use it for user-supplied scalar values — use msg.params for those.

8. Concurrency Model

Queue (Max Concurrency) is the single source of truth for concurrency in this node.

• It defines how many Oracle statements run in parallel.
• The Oracle connection pool is sized to exactly the same value — no hidden extra capacity inside the driver.
• Messages beyond that limit wait in an explicit FIFO queue and are dispatched as slots become free.

9. Message Flow

msg arrives
    │
    ├─ msg.stop === true ──▶ cancel all running queries + drain queue
    │
    ▼
enqueued (FIFO)
    │
    ▼  (slot available)
SQL resolved
  ├─ SQL mode:      use query text as-is
  └─ Nunjucks mode: render template against msg
    │
    ▼
msg.params applied as Oracle bind variables
    │
    ▼
statement executed (autoCommit: true)
    │
    ├─ SELECT  ──▶  msg.payload = array of row objects
    └─ DML     ──▶  msg.payload = []  +  msg.rowsAffected = N
    │
    ▼
msg forwarded downstream

10. Status Indicator

The node status always follows the format:

<queued> (<executing>/<max>)

Colour: blue ring while executing or queued; grey dot when idle.

A growing queue count means the database is slower than the incoming message rate.

11. Cancel Button

The stop icon button in the node editor header cancels all in-flight queries and drops all queued messages.

• Running queries are interrupted by force-closing their Oracle connections (drop: true). This is best-effort — Oracle may have already committed work before the connection is dropped.
• Queued messages are discarded immediately — they are never executed.
• A confirmation dialog appears when queries are active.
• After cancellation the node recovers cleanly and continues processing new messages.
• Cancelled queries are not reported as errors.

The same behaviour is triggered at runtime by sending a message with msg.stop = true.

12. Error Handling

• On execution error, node.error(err, msg) is called with the original message attached.
• The node stays active — the queue continues processing subsequent messages.
• Pool-level errors (connection failures) are reported separately via node.error.
• If the pool is not yet ready when a message arrives, the message is rejected with an explicit error rather than silently queued.

13. Oracle Cloud Wallet Setup

Oracle Cloud Autonomous Database uses mutual-TLS (mTLS) wallet files for secure connectivity. The node handles this natively in Thin mode without requiring any Oracle Client installation.

Steps:

1. Open the Oracle Cloud Console, go to your Autonomous Database, and download the wallet zip.
2. Unzip it to a directory accessible by the Node-RED process (e.g. /opt/oracle/wallet/mydb).
3. In the config.oracle config node:
   • Set Wallet Directory to that directory (must contain cwallet.sso, tnsnames.ora, etc.).
   • Set Connect String to a TNS alias from the wallet's tnsnames.ora — typically mydb_high, mydb_medium, or mydb_low.
   • Set Username and Password to your Oracle database credentials.
   • Set Wallet Password only if your wallet uses ewallet.p12 encryption — leave blank otherwise.

Network reachability is separate from the wallet. The wallet provides mTLS authentication and TNS alias resolution — it does not open a network path to the database. The Oracle endpoint must be reachable from the host running Node-RED. In Oracle Cloud environments this typically requires a private endpoint or adding the Node-RED host's IP to the database's access control list.

14. Common Issues

ORA-00923: FROM keyword not found where expected Oracle requires a FROM clause in every SELECT. Replace SELECT 1 with SELECT 1 FROM DUAL.

ORA-00933: SQL command not properly ended Remove the trailing semicolon (;) from the SQL. The driver does not accept terminal semicolons.

NJS-505: unable to initiate TLS connection The wallet directory is found but the TLS handshake failed. Most likely the wallet password is wrong or missing. Set Wallet Password in the config node.

NJS-500 / connect string not resolved The TNS alias in Connect String was not found in tnsnames.ora. Verify the alias name matches exactly what is in the wallet's tnsnames.ora. Verify Wallet Directory points to the correct unzipped wallet folder.

pool error status on the node The connection pool failed to start. Open the Node-RED debug panel — the full error message is logged there. Common causes: wrong credentials, unreachable database endpoint, or incorrect wallet path.

Node-RED cannot read the wallet directory The Node-RED process must have read access to the wallet directory and all files inside it. Check file permissions.

15. Design Principles

• Predictability over flexibility — one concurrency knob, no hidden limits.
• Explicit queue — backlog is always visible in the status indicator, never hidden inside a driver buffer.
• Safe SQL by default — bind parameters are the recommended path; Nunjucks templating is secondary.
• Fail loudly — errors surface immediately via node.error, never swallowed.
• Minimal footprint — no retries, no fallbacks, no ORM abstractions, no automatic scaling.
• Clear separation of concerns — connection/security settings in the config node; SQL execution settings in the runtime node.
• Thin mode only — no Oracle Instant Client dependency, no driver mode selection.