Node-RED Oracle Database Connector

0.7.6 · active · verified Sun Apr 19

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.

Common errors

```
ORA-06550: line X, column Y: PLS-00103: Encountered the symbol "END" when expecting one of the following:
```
cause 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
```
cause 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
```
cause 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.

Warnings

breaking When 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.
breaking Exporting 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.
gotcha The 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.
breaking Several 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.
gotcha Beginning 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.

Install

npm install node-red-contrib-oracledb-mod npm
yarn add node-red-contrib-oracledb-mod yarn
pnpm add node-red-contrib-oracledb-mod pnpm

Quickstart

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":[]}]

view raw JSON →