lob-bug._js

var oracledb = require('oracledb');
var ez = require('ez-streams');

var cnx = oracledb.getConnection({
	user: "scott",
	password: "oracle",
	connectString: "localhost/ORCL",
}, _);

try {
	cnx.execute("drop table clobs", _);
	console.error("old table dropped");
} catch (err) {
	if (!/^ORA-00942/.test(err.message)) throw err;
}
console.error("creating clobs table");
cnx.execute("create table clobs (id integer, text clob)", _);

function insertClob(_, id, text) {
	console.error("inserting clob id=" + id + ", text=" + text);
	var result = cnx.execute("insert into clobs (id, text) values (:id, empty_clob()) returning text into :text", {
		id: id,
		text: {
			type: oracledb.CLOB,
			dir: oracledb.BIND_OUT
		}
	}, {
		autoCommit: false
	}, _);
	if (result.rowsAffected != 1 || result.outBinds.text.length != 1) {
		throw new Error('Error getting a LOB locator');
	}
	var lob = result.outBinds.text[0];
	ez.devices.string.reader(text).pipe(_, ez.devices.node.writer(lob));
}

insertClob(_, 1, "hello world");
insertClob(_, 2, "how are you doing today?");
insertClob(_, 3, "fine, thank you");
console.error("committing inserted rows");
cnx.execute("commit", _);

console.error("selecting all rows from clobs table");
var rs = cnx.execute("select id, text from clobs", [], {
	resultSet: true,
}, _).resultSet;

while (true) {
	console.error("before rs.getRow(_)");
	var row = rs.getRow(_);
	console.error("after rs.getRow(_) row=" + !!row);
	if (!row) break;
	console.error(row[0] + ": " + ez.devices.node.reader(row[1]).readAll(_));
}

This gist is a repro for oracle/node-oracledb#210

It is written with streamline.js and ez-streams because I write all my code with these tools. So you have to install these with NPM first, and run with _node instead of node (and keep the ._js extension too):

npm install -g streamline
npm install ez-streams
_node --cache lob-bug

With the bugfix that I submitted (checkout the sage branch on my fork), I get:

old table dropped
creating clobs table
inserting clob id=1, text=hello world
inserting clob id=2, text=how are you doing today?
inserting clob id=3, text=fine, thank you
committing inserted rows
selecting all rows from clobs table
before rs.getRow(_)
after rs.getRow(_) row=true
1: hello world
before rs.getRow(_)
after rs.getRow(_) row=true
2: how are you doing today?
before rs.getRow(_)
after rs.getRow(_) row=true
3: fine, thank you
before rs.getRow(_)
after rs.getRow(_) row=false

Without the fix I get:

old table dropped
creating clobs table
inserting clob id=1, text=hello world
inserting clob id=2, text=how are you doing today?
inserting clob id=3, text=fine, thank you
committing inserted rows
selecting all rows from clobs table
before rs.getRow(_)
after rs.getRow(_) row=true
1: hello world
before rs.getRow(_)
Segmentation fault: 11

Notes:

• you need to apply https://github.com/oracle/node-oracledb/pull/209/files fix first. Otherwise it will fail with a "stream closed unexpectedly" message.
• I can rewrite without streamline / ez-streams but it will just make the code a little heavier.