X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

  • node.js |
    Monday, August 31, 2015

Using DBMS_OUTPUT with Node.js and node-oracledb

By: Christopher Jones | Senior Principal Product Manager

The DBMS_OUTPUT package is the standard way to "print" output from PL/SQL. The way DBMS_OUTPUT works is like a buffer. Your Node.js application code turns on DBMS_OUTPUT buffering, calls some PL/SQL code that puts text into the buffer, and then later fetches from that buffer. Note: any PL/SQL code that calls DBMS_OUTPUT runs to completion before any output is available to the user. Also, other database connections cannot access your buffer.

A basic way to fetch DBMS_OUTPUT with node-oracledb is to bind an output string when calling the PL/SQL dbms_output.get_line() procedure, print the string, and then repeat until there is no more output. Another way that I like is to wrap the dbms_output.get_line() call into a pipelined function and fetch the DBMS_OUTPUT using a SQL query.

The following code shows both methods.

/*
NAME
dbmsoutput.js
DESCRIPTION
Shows two methods of displaying PL/SQL DBMS_OUTPUT in node-oracledb.
The second method depends on these PL/SQL objects:
create or replace type dorow as table of varchar2(32767);
/
show errors
create or replace function mydofetch return dorow pipelined is
line varchar2(32767);
status integer;
begin loop
dbms_output.get_line(line, status);
exit when status = 1;
pipe row (line);
end loop;
return; end;
/
show errors
*/
'use strict';
var async = require('async');
var oracledb = require('oracledb');
var dbconfig = require('./dbconfig.js');
oracledb.createPool(
dbconfig,
function(err, pool) {
if (err)
console.error(err.message)
else
doit(pool);
});
var doit = function(pool) {
async.waterfall(
[
function(cb) {
pool.getConnection(cb);
},
// Tell the DB to buffer DBMS_OUTPUT
enableDbmsOutput,
// Method 1: Fetch a line of DBMS_OUTPUT at a time
createDbmsOutput,
fetchDbmsOutputLine,
// Method 2: Use a pipelined query to get DBMS_OUTPUT
createDbmsOutput,
function(conn, cb) {
executeSql(
conn,
"select * from table(mydofetch())", [], { resultSet: true}, cb);
},
printQueryResults
],
function (err, conn) {
if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }
conn.release(function (err) { if (err) console.error(err.message); });
}
)
};
var enableDbmsOutput = function (conn, cb) {
conn.execute(
"begin dbms_output.enable(null); end;",
function(err) { return cb(err, conn) });
}
var createDbmsOutput = function (conn, cb) {
conn.execute(
"begin "
+ "dbms_output.put_line('Hello, Oracle!');"
+ "dbms_output.put_line('Hello, Node!');"
+ "end;",
function(err) { return cb(err, conn) });
}
var fetchDbmsOutputLine = function (conn, cb) {
conn.execute(
"begin dbms_output.get_line(:ln, :st); end;",
{ ln: { dir: oracledb.BIND_OUT, type:oracledb.STRING, maxSize: 32767 },
st: { dir: oracledb.BIND_OUT, type:oracledb.NUMBER } },
function(err, result) {
if (err) {
return cb(err, conn);
} else if (result.outBinds.st == 1) {
return cb(null, conn); // no more output
} else {
console.log(result.outBinds.ln);
return fetchDbmsOutputLine(conn, cb);
}
});
}
var executeSql = function (conn, sql, binds, options, cb) {
conn.execute(
sql, binds, options,
function (err, result) {
if (err)
cb(err, conn)
else
cb(null, conn, result);
});
}
var printQueryResults = function(conn, result, cb) {
if (result.resultSet) {
fetchOneRowFromRS(conn, result.resultSet, cb);
} else if (result.rows && result.rows.length > 0) {
console.log(result.rows);
return cb(null, conn);
} else {
console.log("No results");
return cb(null, conn);
}
}
function fetchOneRowFromRS(conn, resultSet, cb) {
resultSet.getRow( // note: getRows would be more efficient
function (err, row) {
if (err) {
cb(err, conn);
} else if (row) {
console.log(row);
fetchOneRowFromRS(conn, resultSet, cb);
} else {
cb(null, conn);
}
});
}

The output is:

Hello, Oracle!
Hello, Node!
[ 'Hello, Oracle!' ]
[ 'Hello, Node!' ]

I used resultSet.getrow() for simplicity, but you will probably want to use resultSet.getRows() for efficiency. If you want to buffer all the output in the Node.js application, Bruno Jouhier has a nice implementation to build up an array of query output in his GitHub gist query-all.js.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha