Registry / database / node-red-contrib-oracledb-mod

node-red-contrib-oracledb-mod

library0.7.6jsnpmunverified

node-red-contrib-oracledb-mod provides robust and modern Node-RED nodes for seamless interaction with Oracle Database. It offers nodes for connecting to Oracle databases, executing SQL/PLSQL queries, and handling stored procedures/functions. Key features include resilient connection pooling to manage network disconnects and database restarts, support for DML operations (SELECT, INSERT, UPDATE, DELETE), and advanced data binding via `msg.bindVars`. The package currently stands at version 0.7.6, with releases addressing critical bugs and enhancing usability. It differentiates itself through its focus on stability, comprehensive Oracle feature support, and ease of integration into Node-RED flows. This package relies on the `oracledb` Node.js driver internally.

database

npm install node-red-contrib-oracledb-mod

github ↗package ↗

INSTALL

IMPORT

SIG · NODE-RED-CONTRIB-O

node-red-contrib-oracledb-mod

databasejavascriptv0.7.6

harness data pending

Install & Compatibility

Where this runs

No compatibility data collected yet for this library.

Code

Verified usage

This Node-RED flow demonstrates a basic `SELECT` query using `node-red-contrib-oracledb-mod`. It configures an Oracle connection (`oracle-server`) and uses an `oracledb` node to execute a parameterized query, sending the results to a debug tab. The Oracle Instant Client path and database credentials should be configured via environment variables or directly in the configuration node.

[{"id":"d1a8c3d1.abcd","type":"tab","label":"Oracle DB Quickstart","disabled":false,"info":""},{"id":"e1f2g3h4.ijkl","type":"oracle-server","name":"My Oracle Connection","connectionType":"Classic","connectString":"localhost:1521/ORCLPDB1","user":"system","password":"$(ORACLE_PASSWORD)","instantClientPath":"/opt/oracle/instantclient_21_13","poolMin":"2","poolMax":"10","poolTimeout":"60","x":200,"y":100,"wires":[]},{"id":"a1b2c3d4.efgh","type":"inject","name":"Fetch Employees","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":200,"wires":[["m1n2o3p4.qrst"]]},{"id":"m1n2o3p4.qrst","type":"function","name":"Prepare Query","func":"msg.query = 'SELECT employee_id, first_name, last_name, email FROM employees WHERE department_id = :1';\nmsg.payload = [60]; // Example department_id\nreturn msg;","outputs":1,"noerr":0,"x":360,"y":200,"wires":[["u5v6w7x8.yzab"]]},{"id":"u5v6w7x8.yzab","type":"oracledb","name":"Execute Query","server":"e1f2g3h4.ijkl","action":"send single query result message","bindVars":"","x":580,"y":200,"wires":[["q1r2s3t4.uvwx"]]},{"id":"q1r2s3t4.uvwx","type":"debug","name":"Results","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":200,"wires":[]}]

Debug

Known issues

breakingWhen executing PL/SQL blocks containing `BEGIN...END;` statements, older versions (<0.7.6) incorrectly removed trailing semicolons, leading to `ORA-06550` compilation errors. This was due to a blanket rule to prevent `ORA-00933` for regular SQL. The fix intelligently preserves semicolons for PL/SQL blocks.

fix

Upgrade to v0.7.6 or newer. Ensure your PL/SQL blocks are correctly terminated with semicolons; the node will now handle them properly.

affects: <0.7.6

breakingExporting and re-importing flows in versions <0.7.5 could cause a Node-RED server crash due to `NJS-007: invalid value` errors. Connection pool properties (e.g., `poolMax`, `poolMin`) were saved as strings in flow JSON but expected as numbers by the backend.

fix

Upgrade to v0.7.5 or newer. After upgrading, you may need to re-save affected `oracle-server` configuration nodes to ensure properties are stored correctly as numbers.

affects: <0.7.5

gotchaThe Oracle Instant Client libraries are a mandatory prerequisite for this package and must be installed on the same machine running Node-RED. Failure to install and correctly configure the `LD_LIBRARY_PATH` (Linux), `PATH` (Windows), or `DYLD_LIBRARY_PATH` (macOS) environment variables, and provide the 'Instant Client Path' in the `oracle-server` node, will prevent the nodes from functioning.

fix

Follow the installation steps for Oracle Instant Client: download Basic or Basic Light, unzip to a permanent location, and set the appropriate environment variable (LD_LIBRARY_PATH, PATH, DYLD_LIBRARY_PATH). Crucially, also configure the 'Instant Client Path' field in your `oracle-server` configuration node in Node-RED.

affects: >=0.1.0

breakingSeveral upgrade issues in older versions (0.7.0, 0.7.1, 0.7.2) caused existing server configurations to be marked as invalid after updating. This required users to manually reconfigure or update their `oracle-server` nodes.

fix

Upgrade to v0.7.3 or newer. For affected versions, manually edit and re-save your `oracle-server` configuration nodes to resolve validation errors. In some cases, a command line `npm install node-red-contrib-oracledb-mod@latest` and Node-RED restart was required.

affects: 0.7.0 - 0.7.2

gotchaBeginning with v0.7.4, the `oracledb` node includes 'Smart Named Binding,' allowing a `msg.payload` object to be directly used for `INSERT` or `UPDATE` queries with named binds (e.g., `VALUES (:name, :email)`). While this is a feature, it's a change in how `msg.payload` might be interpreted for queries and could require adjusting existing flows that relied on positional binding with array payloads if named binding is now implicitly triggered.

fix

Review existing `INSERT` or `UPDATE` flows when upgrading to v0.7.4+. If using named binds, ensure `msg.payload` is an object where keys match bind names. If using positional binds, `msg.payload` should remain an array.

affects: >=0.7.4

Errors

Common errors & fixes

ORA-06550: line X, column Y: PLS-00103: Encountered the symbol "END" when expecting one of the following:

Executing PL/SQL blocks with `BEGIN...END;` statements where trailing semicolons were incorrectly removed by the node's query processing logic in older versions.

fix

Upgrade `node-red-contrib-oracledb-mod` to version 0.7.6 or newer. The node now intelligently preserves semicolons for PL/SQL blocks.

NJS-007: invalid value for property

Occurs during Node-RED startup or flow import (versions <0.7.5) when `oracle-server` connection pool properties (like `poolMax`, `poolMin`) were saved as strings in the flow JSON instead of numbers.

fix

Upgrade `node-red-contrib-oracledb-mod` to version 0.7.5 or newer. After upgrading, open and re-save any affected `oracle-server` configuration nodes in the Node-RED editor to ensure properties are correctly persisted as numbers.

Invalid node config error / server configuration invalid

Known bug in versions 0.7.0, 0.7.1, and 0.7.2 where existing `oracle-server` configurations were incorrectly flagged as invalid after a package upgrade.

fix

Upgrade `node-red-contrib-oracledb-mod` to version 0.7.3 or newer. If already affected, open and re-save the `oracle-server` configuration node(s) in the Node-RED editor. A manual `npm install node-red-contrib-oracledb-mod@latest` in your Node-RED user directory followed by a restart of Node-RED might be necessary.

Upgrade

Version history

0.7.6latest on npm

Audit

Dependencies

oracledbrequiredProvides the underlying Node.js driver for Oracle Database connectivity.

Agent activity

5 hits · last 30 days

node

OpenAI (training)

Resources

githubhttps://github.com/vtulluru/node-red-contrib-oracledb-mod ↗

packagehttps://www.npmjs.com/package/node-red-contrib-oracledb-mod ↗