Sunday Aug 30, 2015

Using DBMS_OUTPUT with Node.js and node-oracledb

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.

About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Mail: christopher.jones@oracle.com
Twitter: @ghrd
OTN: Scripting Languages
Book: Free PHP Oracle book

Follow:
Blaine Carter
Dan McGhan

Search

Archives
« May 2016
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today