Skip navigation.

Christopher Jones

Syndicate content
Notes on the Scripting Languages and Oracle Database. I cover Node.js, PHP, Python, Ruby, Perl, Apache, Linux
Updated: 11 hours 34 min ago

Installing node-oracledb on OS X with Oracle Instant Client 11.2.0.4

Sun, 2015-07-26 22:17

I've been hacking an Apple OS X shell script to install node-oracledb. You tell it where your Instant Client libraries and headers ZIP packages are. It then installs node-oracledb, resulting in an instantclient directory and a node_modules directory. This automates the instructions Node-oracledb Installation on OS X with Instant Client.

My osxinstall.sh script can be seen here.

I was investigating how to avoid needing to set DYLD_LIBRARY_PATH. I wanted to find how to replicate the use of rpath, which is available for node-oracledb on Linux. A standard install on OS X needs DYLD_LIBRARY_PATH set, otherwise Node.js will fail with the error:

   cjones@cjones-mac:~/n$ node select1.js

   /Users/cjones/n/node_modules/oracledb/lib/oracledb.js:28
       throw err;
	     ^
   Error: dlopen(/Users/cjones/n/node_modules/oracledb/build/Release/oracledb.node, 1):
           Library not loaded: /ade/b/3071542110/oracle/rdbms/lib/libclntsh.dylib.11.1
     Referenced from: /Users/cjones/n/node_modules/oracledb/build/Release/oracledb.node
     Reason: image not found
       at Module.load (module.js:356:32)
       at Function.Module._load (module.js:312:12)
       at Module.require (module.js:364:17)
       at require (module.js:380:17)
       at Object.<anonymous> (/Users/cjones/n/node_modules/oracledb/lib/oracledb.js:23:15)
       at Module._compile (module.js:456:26)
       at Object.Module._extensions..js (module.js:474:10)
       at Module.load (module.js:356:32)
       at Function.Module._load (module.js:312:12)
       at Module.require (module.js:364:17)

Before running osxinstall.sh, edit it and set the paths to where the Instant Client 11.2.0.4 'basic' and 'sdk' ZIP files are located on your filesystem, see IC_BASIC_ZIP and IC_SDK_ZIP. (You can download Instant Client from OTN. Use the 64-bit packages). You also specify the target application directory you are using, see TARGET_DIR. This is where the components are installed into. Update https_proxy if you are behind a firewall, otherwise comment it out.

If you have various node_modules directories around, then npm might end up installing oracledb in an unexpected place and the script will error.

The key bit of osxinstall.sh that I was interested in is:

    # For Oracle Instant Client 11.2.0.4: these are the default paths we will change
    IC_DEF1=/ade/b/3071542110/oracle/rdbms/lib
    IC_DEF2=/ade/dosulliv_ldapmac/oracle/ldap/lib

    . . .

    # Warning: work in progress - may not be optimal
    chmod 755 $OCI_LIB_DIR/*dylib $OCI_LIB_DIR/*dylib.11.1
    install_name_tool -id libclntsh.dylib.11.1 $OCI_LIB_DIR/libclntsh.dylib.11.1
    install_name_tool -change $IC_DEF2/libnnz11.dylib $OCI_LIB_DIR/libnnz11.dylib \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1
    install_name_tool -id libnnz11.dylib $OCI_LIB_DIR/libnnz11.dylib
    install_name_tool -change $IC_DEF1/libclntsh.dylib.11.1 \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1 $OCI_LIB_DIR/libociei.dylib
    install_name_tool -change $IC_DEF1/libclntsh.dylib.11.1 \
                 $OCI_LIB_DIR/libclntsh.dylib.11.1 $NODE_ORACLEDB_LIB
    chmod 555 $OCI_LIB_DIR/*dylib $OCI_LIB_DIR/*dylib.11.1

This changes the library install and identification names using install_name_tool. Note this tool cannot allocate more space for path names than currently exists. My code is a work in progress; I may work out a better way, perhaps using libtool. Comments & suggestions welcome.

The script does more than most people probably need. In future even I might only run parts extracted from it.

If you are new to node-oracledb, check out its install and API documentation on GitHub. You may also be interested in reading The Easiest Way to Install Oracle Database on Mac OS X.

node-oracledb 0.7.0 now supports Result Sets and REF CURSORS

Mon, 2015-07-20 16:58

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.

Python cx_Oracle 5.2 driver for Oracle Database has been released

Mon, 2015-06-22 17:03

Anthony Tuininga just released an updated Python cx_Oracle 5.2 driver for Oracle Database. This release brings a number of enhancements, many of them for Oracle Database 12c features such as longer VARCHARS.

cx_Oracle 5.2 is the first release Oracle has contributed code for (thanks Avinash!) so we're pretty happy all around. And a big thank you to all the other contributors and users who have made this release possible.

The new code features are:

  • Support for Oracle Database 12c strings up to 32k characters.
  • Support for LOB values larger than 4 GB.
  • Support for Oracle Database 12c array DML row counts.
  • Support for fetching batch errors.
  • Support for connections as SYSASM.
  • Added types NCHAR, FIXED_NCHAR and LONG_NCHAR to replace the types UNICODE, FIXED_UNICODE and LONG_UNICODE (which are now deprecated). These types are available in Python 3 as well so they can be used to specify the use of NCHAR type fields when binding or using setinputsizes().
  • Support for building without any configuration changes to the machine when using instant client RPMs on Linux.
  • Fixed session releasing to the pool when calling connection.close() (Issue #2)
  • Fixed binding of booleans in Python 3.x.
  • Added __version__ attribute to conform with PEP 396.
  • Fixed handling of datetime intervals (Issue #7)

The complete release notes are here.

My favorite feature is the installation improvement. (Disclaimer: I contributed the initial implementation!) With this change, Instant Client RPMS on Linux can now be used. The best bit is cx_Oracle will automatically locate Instant Client and will then also automatically build using rpath. The installation of cx_Oracle on Linux is now as simple as installing the Instant Client Basic & SDK RPMs, and running 'pip install cx_Oracle'. No need to set ORACLE_HOME during installation. No need to set LD_LIBRARY_PATH at runtime. If you have a Linux ULN support subscription you can install Instant Client via yum, which makes it even simpler.

Users of Database Resident Connection Pooling will like the connection.close() fix since it allows DRCP to be used effectively without requiring a cx_Oracle session pool.

In summary the cx_Oracle 5.2 release adds great features to the already impressive Oracle Database support available to Python applications. Application development and deployment just got better.

Resources:

Thanks again to Anthony, Avinash and all the contributors who have made cx_Oracle so good.

Scripting Languages & Oracle: Blogs to Follow

Wed, 2015-06-17 13:58
If you haven't come across them, keep an eye on the blogs of Blaine Carter and Dan McGhan. They will be posting mostly on developing with Python and Javascript, respectively. Blaine & Dan work at Oracle under the leadership of Steven Feuerstein who is well known in the Oracle PL/SQL world for his enthusiasm for application development.

node-oracledb 0.6.0 is on NPM (Node.js driver for Oracle Database)

Tue, 2015-05-26 16:31

Node-oracledb 0.6.0 is now out on NPM. The Oracle Database Node.js driver powers high performance Node.js applications.

There is one feature change in this release: node-oracledb now builds with Node.js 0.10, 0.12 and with io.js. Huge thanks to Richard Natal for his GitHub pull request that added support.

For more information about node-oracledb see the node-oracledb GitHub page.

node-oracledb 0.5.0 is on NPM (Node.js driver for Oracle Database)

Fri, 2015-05-08 09:49

It's been a long time between drinks so we are bringing out the 0.5.0 Preview Release version of the Node.js driver for Oracle Database, warts and all.

  • Changed the isAutoCommit attribute name to autoCommit.

    Changed the isExternalAuth attribute name to externalAuth.

    These two small attribute name changes break backwards compatibility with node-oracledb 0.4.2. A simple search and replace in your code base is all that is needed to migrate to 0.5.0.

    We wanted to make these changes while the driver is still technically in a Preview Release status. I warned about the change in a GitHub post.

  • Fixed outBinds array counting to not give empty array entries for IN binds. When a bind-by-position -style array of values is passed in as the execute() function bind parameter, the resulting outBinds array used to contain empty positions corresponding to each IN bind. Now it just has the OUT binds. Your code may need updating.

  • Added support for "DML RETURNING" bind variables like UPDATE test SET NAME = 'abc' RETURNING id INTO :idbv. See the documentation for details. Make sure that maxSize is big enough for any STRING OUT binds otherwise you will get an error. We're continuing to investigate some edge cases with this code but wanted to get it out for early adopters to look at. Again, this is a preview release.

  • Rectified the error message for invalid type properties.

  • To help you keep track of changes, a CHANGELOG file was added.

For more information about node-oracledb see the node-oracledb GitHub page.

Upcoming Webinar on April 15th about Node-oracledb driver for Node.js

Tue, 2015-04-14 16:24

Update:
   Watch the recording on the Oracle Database Development Web Series YouTube channel

Tomorrow I'll be giving a webinar covering node-oracledb, the Node.js driver for Oracle database.

Date: Wednesday, April 15th
Time: 9am (San Francisco time)
Webex - No need to register. Session will be recorded.
US Toll Free Audio (1-866-682-4770), with international numbers available (Meeting ID: 8232385# & PIN: 123456#)
Speaker: Christopher Jones
Topic:

Introduction to node-oracledb: the new Node.js driver for Oracle Database

Want to write highly scalable, event driven applications? Node.js lets you do just that.

After a quick introduction to Node.js, this session dives into the features of node-oracledb, the new, open source Node.js driver for Oracle Database, which is under active development.

To join the webinar, go to Webex

The webinar is part of an ongoing weekly series of developer sessions on a variety of topics Oracle Database Development Web Series - it's worth keeping an eye on the schedule.

A quick link to the node-oracledb homepage is http://ora.cl/wHu