X

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

node-oracledb 0.7.0 now supports Result Sets and REF CURSORS

Christopher Jones
Senior Principal Product Manager

A new release of the Node.js driver for Oracle Database is now on npmjs.com and GitHub.

node-oracledb 0.7 connects Node.js 0.10, Node.js 0.12, and io.js to Oracle Database. It runs on a number of platforms. For more information about node-oracledb see the node-oracledb GitHub page.

The changes in 0.7 are:

  • Added result set support for fetching large data sets. Rows from queries can now be fetched in batches using a ResultSet class. This allows large query results to be fetched without requiring all values to be in memory at once. New getRow() and getRows() methods can be called repeatedly to scroll through the query results.

    The original node-oracledb behavior of returning all rows at once remains the default. To return a resultSet, use the new execute() option { resultSet: true }. For example:

    //  (See the full code in examples/resultset2.js)
    . . .
    var numRows = 10; // number of rows to return from each call to getRows()
    connection.execute(
    "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
    [], // no bind variables{ resultSet: true }, // return a result set. Default is false
    function(err, result)
    {
    if (err) { . . . }
    fetchRowsFromRS(connection, result.resultSet, numRows);
    });
    });
    . . .
    function fetchRowsFromRS(connection, resultSet, numRows)
    {resultSet.getRows( // get numRows rows
    numRows,
    function (err, rows)
    {
    if (err) {
    . . . // close the result set and release the connection
    } else if (rows.length == 0) { // no rows, or no more rows
    . . . // close the result set and release the connection
    } else if (rows.length > 0) {
    console.log(rows);
    fetchRowsFromRS(connection, resultSet, numRows); // get next set of rows
    }
    });
    }

    It's important to use the new resultSet close() method to close the result set when no more data is available or required.

    There is more information on Result Sets in the manual.

  • Added REF CURSOR support for returning query results from PL/SQL. PL/SQL code that returns REFCURSOR results via bind parameters can now bind a new node-oracledb type Oracledb.CURSOR and fetch the results using the new ResultSet class.

    //  (See the full code in examples/refcursor.js)
    var oracledb = require('oracledb');
    . . .
    var numRows = 10; // number of rows to return from each call to getRows()
    var bindvars = {
    sal: 6000,
    cursor: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT }
    }
    connection.execute(
    "BEGIN get_emp_rs(:sal, :cursor); END;", // The PL/SQL has an OUT bind of type SYS_REFCURSOR
    bindvars,
    function(err, result)
    {
    if (err) { . . . }
    fetchRowsFromRS(connection, result.outBinds.cursor, numRows);
    });
    . . .
    function fetchRowsFromRS(connection, resultSet, numRows)
    {resultSet.getRows( // get numRows rows
    numRows,
    function (err, rows)
    {
    if (err) {
    . . . // close the result set and release the connection
    } else if (rows.length == 0) { // no rows, or no more rows
    . . . // close the result set and release the connection
    } else if (rows.length > 0) {
    console.log(rows);
    fetchRowsFromRS(connection, resultSet, numRows); // get next set of rows
    }
    });
    }

    There is more information on using REF CURSORS in the manual.

  • Added row prefetching support. The new ResultSet class supports prefetching via a new attribute oracledb.prefetchRows and a new execute() option prefetchRows. Each time the application fetches query or REF CURSOR rows in a ResultSet from Oracle Database, prefetching allows the underlying Oracle libraries to transfer extra rows. This allows better use of database and network resources, improving performance and scalability. Regardless of the prefetch size, the number of rows returned to the application does not change. Buffering is handled by the underlying Oracle client library.

    The default prefetch size is 100 extra rows. Applications should tune the prefetch size used by each execute() for desired performance and/or to avoid allocating and initializing unused memory. There are some more tips in the manual.

    With node-oracledb 0.7.0, non-ResultSet queries now use prefetching with a fixed size of 2. This should reduce the number of round trips required for these queries.

  • Added a test suite. Yay! See the README in the tests directory for how to run the tests. When you run the test suite, you'll notice each test has a unique number for ease of identification. The numbers are not necessarily sequential.

    We do most testing on Linux and Windows. If you see test output differences due to environment or version differences, please sign the OCA and submit a pull request with the fix and an explanation of why it is needed. See CONTRIBUTING.

    If you submit new tests (after signing the OCA), assign each one a unique number in the documented range that applies to the area being tested.

  • Fixed error handling for SQL statements using RETURNING INTO. A bug causing all errors with DML RETURNING statements to report the same error message was fixed.

  • Fixed INSERT of a date when the SQL has a RETURNING INTO clause. When using an INSERT to insert a date or timestamp and the SQL clause had a RETURNING INTO clause for character or number columns, then an error was being thrown. This has been fixed.

  • Renumbered the values used by the Oracledb Constants. If your application uses constant names such as Oracledb.OBJECT or Oracledb.BIND_INOUT then you won't notice the change. However if, for some reason, code has hardcoded numbers like 2, then you will have to update to use the new numbers, see lib/oracledb.js. Or, better, change the code to use the constants' names.

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.