How to handle a lot of result rows in Oracle Database with Node,js

Question

I'm doing a query to a oracle database but there are a lot of results rows so I'm getting a JavaScript heap out of memory error.

Is there a way to handle the result of the query by batch or something for not get this out of memory error?

Answer 1

You didn't specify, but I'm guessing you're using node-oracledb. If so, then the trick is to use a ResultSet object. This will provide a read-consistent view of the data (a single point in time view of the data from the time the query started) while allowing you to stream data across the wire: https://oracle.github.io/node-oracledb/doc/api.html#streamingresults

Here's an example that uses a QueryStream instance. The QueryStream class is just a wrapper on a ResultSet to provide streaming APIs.

var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');

var rowcount = 0;

oracledb.getConnection(
  {
    user          : dbConfig.user,
    password      : dbConfig.password,
    connectString : dbConfig.connectString
  },
  function(err, connection) {
    if (err) {
      console.error(err.message);
      return;
    }

    var stream = connection.queryStream(
      'SELECT first_name, last_name FROM employees ORDER BY employee_id',
      [],  // no binds
      { fetchArraySize: 150 }  // internal buffer size for performance tuning
    );

    stream.on('error', function (error) {
      // console.log("stream 'error' event");
      console.error(error);
      return;
    });

    stream.on('metadata', function (metadata) {
      // console.log("stream 'metadata' event");
      console.log(metadata);
    });

    stream.on('data', function (data) {
      // console.log("stream 'data' event");
      console.log(data);
      rowcount++;
    });

    stream.on('end', function (metadata) {
      // console.log("stream 'end' event");
      stream.destroy();  // the stream should be closed when it has been finished
    });

    stream.on('close', function () {
      // console.log("stream 'close' event");
      console.log('Rows selected: ' + rowcount);
      connection.close(  // Note: do not close connections on 'end'
        function(err) {
          if (err) {
            console.error(err.message);
          }
        });
    });
  });

You're probably going to be streaming the results to a file or an HTTP response object. In either case, you'll likely want proper JSON rather than the individual rows that the driver returns. Have a look at this issue for an example of how you can do that: https://github.com/oracle/node-oracledb/issues/908#issuecomment-390006986