Sunday Jul 26, 2015

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

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)

So, I was playing with osxinstall.sh to see how to circumvent this. 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.

Monday Jul 20, 2015

node-oracledb 0.7.0 now supports Result Sets and REF CURSORS

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.

Monday Jun 22, 2015

Python cx_Oracle 5.2 driver for Oracle Database has been released

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.

Tuesday May 26, 2015

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

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.

Friday May 08, 2015

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

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.

Tuesday Apr 14, 2015

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

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

Saturday Mar 28, 2015

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

The 0.4.2 version of the Node.js driver for Oracle Database is out.

  • Node-oracledb is now officially on the npmjs.com repository. This simplifies the Install instructions by removing the need to manually clone or download from GitHub. Thanks to Tim Branyen for setting this up and handing over stewardship to us.

  • Metadata support was added. Column names are now provided in the execute() callback result object. See the doc example.

  • We saw a few people try to use strangely old versions of Node 0.10. I've bumped up the lower limit requirement a bit. It won't force you to use the latest Node.js 0.10 patch set but you really should keep up to date with security fixes.

    If you want to build with Node 0.12, there is a community contributed patch from Richard Natal that can be found here. This patch also allows node-oracledb to work with io.js.

  • The default Instant Client directory on AIX was changed from /opt/oracle/instantclient_12_1 to /opt/oracle/instantclient. This now matches the default of other platforms.

  • One other small change was some improvements to the Windows install documentation.

Yes, work is continuing behind the scenes on other features.

Thursday Feb 19, 2015

Running Carsten Czarski's node-oracledb WebSocket Example

My colleague Carsten Czarski recently presented on the node-oracledb driver for Node.js. One of his demos used WebSockets. It was a live demo, not captured in slides. I thought I'd explain how I got it to run in my Oracle Linux 64 bit environment.

  • Download and extract the Node 0.10.36 bundle from here. (At time of writing, the node-oracle driver requires Node.js 0.10). Add the bin to your PATH, for example:

    $ export PATH=/opt/node-v0.10.36-linux-x64/bin:$PATH
    
  • Download and install the 'basic' and 'devel' Instant Client RPMs from OTN:

    # rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
    # rpm -ivh oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
    
  • Download Carsten's demo code from here and extract it:

    $ cd /home/cjones
    $ mkdir wsdemo
    $ cd wsdemo
    $ mv $HOME/Downloads/nodejs-beispielprogramme.zip .
    $ unzip nodejs-beispielprogramme.zip
    
  • Create a new package.json file:

    {
        "name": "ccwebsockets",
        "version": "1.0.0",
        "description": "Carsten's WebSocket Demo application using node-oracledb 0.3.1.",
        "scripts": {
    	"start": "node 05-websockets.js"
        },
        "dependencies": {
    	"oracledb": "oracle/node-oracledb#v0.3.1",
    	"websocket": "^1.0",
    	"express": "^4.11"
        }
    }
    
  • Edit 05-websockets.js and change the database credentials at line 111. The schema needs to have the EMP table.

    oracledb.createPool(
      {
        user          : "scott",
        password      : "tiger",
        connectString : "localhost/pdborcl",
        poolMin       : 5,
        poolMax       : 10
      },
    
  • Also in 05-websockets.js, change the path name at line 65 to your current directory name:

    filename = path.join("/home/cjones/wsdemo", uri);
    
  • Use npm to automatically install the node-oracle driver and the "websocket" and "express" dependencies listed in package.json:

    $ npm install
    
  • To run the demo, use the package.json script "start" target to load 05-websockets.js:

    $ npm start
    

    The server will start:

    > ccwebsockets@1.0.0 start /home/cjones/wsdemo
    > node 05-websockets.js
    
    Websocket Control Server listening at http://0.0.0.0:9000
    Database connection pool established
    
  • Open a couple of browser windows to http://127.0.0.1:9000/html/websocket.html. These are the clients listening for messages.

    The output is the starting point of the demo. Let's send a message to those clients.

  • Open a third browser window for the URL http://127.0.0.1:9000/update/CLARK. The two listening windows will be updated with the "message" containing the query result payload. My screenshot shows this, and also has evidence that I had previously visited http://127.0.0.1:9000/update/KING :

You might have noticed the screen shots were made on OS X. If you are not on Linux, refer to INSTALL to see how to install Node.js and node-oracledb. The package.json file I created will download node-oracledb 0.3.1 so you don't need to manually get it from GitHub. You will have to set OCI_LIB_DIR and OCI_INC_DIR during installation, and then set LD_LIBRARY_PATH, DYLD_LIBRARY_PATH or PATH when you want to run node.

You can follow Carsten at @cczarski.

Monday Feb 16, 2015

node-oracledb 0.3.1 is on GitHub (Node.js driver for Oracle Database)

On behalf of the development team, I have merged some new features and fixes to node-oracledb

Updates for node-oracledb 0.3.1

  • Added Windows build configuration. See Node-oracledb Installation on Windows. Thanks to Rinie Kervel for submitting a pull request, and thanks to all those that commented and tested.
  • Added Database Resident Connection Pooling (DRCP) support. See API Documentation for the Oracle Database Node.js Driver

    "Database Resident Connection Pooling enables database resource sharing for applications that run in multiple client processes or run on multiple middle-tier application servers. DRCP reduces the overall number of connections that a database must handle. DRCP is distinct from node-oracledb's local connection pool. The two pools can be used separately, or together.
  • Made an explicit connection release() do a rollback, to be consistent with the implicit release behavior.

  • Made install on Linux look for Oracle libraries in a search order:

    • Using install-time environment variables $OCI_LIB_DIR and $OCI_INC_DIR
    • In the highest version Instant Client RPMs installed
    • In $ORACLE_HOME
    • In /opt/oracle/instantclient
  • Added RPATH support on Linux, so LD_LIBRARY_PATH doesn't always need to be set. See Advanced installation on Linux

  • The directory name used by the installer for the final attempt at locating an Instant Client directory is now /opt/oracle/instantclient or C:\oracle\instantclient. This path may be used if OCI_DIR_LIB and OCI_INC_LIB are not set and the installer has to guess where the libraries are.

  • Added a compile error message "Oracle 11.2 or later client libraries are required for building" if attempting to build with older Oracle client libraries. This helps developers self-diagnose this class of build problem.

  • Fixed setting the isAutoCommit property.

  • Fixed a crash using pooled connections on Windows.

  • Fixed a crash querying object types.

  • Fixed a crash doing a release after a failed terminate. (The Pool is still unusable - this will be fixed later)

  • Clarified documentation that terminate() doesn't release connections. Doing an explicit release() of each pooled connection that is no longer needed is recommended to avoid resource leaks and maximize pool usage.

  • Updated version to 0.3.1 (surprise!)

Thursday Jan 22, 2015

A Personal Victory: Oracle Database Sample Schemas are on GitHub

For anyone who ever deleted a row from a table in Oracle's Sample HR schema and wanted it back, help is nearby. You no longer have to download the full "Oracle Database 12c Release 1 Examples" zip (499,228,127 bytes worth for the Linux bundle) and run the Oracle installer. Now you can clone our GitHub db-sample-schema repository and run the creation SQL scripts in SQL*Plus.

This new repository installs these six sample schemas:

  • HR: Human Resources
  • OE: Order Entry
  • PM: Product Media
  • IX: Information Exchange
  • SH: Sales History
  • BI: Business Intelligence

Because of the widespread use of these schemas, we did minimal changes to the bundle. The install, as it is given, installs all schemas and needs to be done on a database server since file system access is needed from the database.

But now, if you want, you can fork the repo and modify it to install just the HR schema from a client machine. Or change your fork to install the HR schema into an arbitrary user name of your choice so multiple people can test the same data set. And what about modifying the script to do DROP TRIGGER SECURE_EMPLOYEES getting rid of that annoying time-based trigger which yells 'You may only make changes during normal office hours' if you try to make changes after 6pm or on weekends? This may be a great teaching tool about triggers but not useful when you are configuring demonstrations for big conferences late into the night!

And why is this a personal victory? Because as a client tool person, how to find these schema creation scripts has irked me in the past. The HR schema replaced SCOTT/TIGER in the Oracle documentation a long time ago but was not easily available to use. I've written a lot of examples using HR but never had a good way to instruct how to install the schema. I'm glad to have helped (being partially modest here about the legal and administrative things it required) getting this small set of scripts out on GitHub. If it makes it easier for someone to talk about features or issues by reference to a common data set, then my job is done. Having the scripts readily available is also a reminder to the Oracle community to share information and knowledge efficiently. Even as we head to a world of cloneable databases and snapshots, sometimes it is just easier to run a SQL script.

This repo is a piece of a jigsaw, and it can be used where it fits. The schemas could be now considered "traditional". In future, Oracle Database teams will continue to create fresh data sets to show off newer and upcoming database features, such as these analytical-sql-examples that you might be interested in.

Tuesday Jan 20, 2015

Introducing node-oracledb - a Node.js driver for Oracle Database

When we announced we were working on a Node.js driver for Oracle Database, it was to a large, enthusiastic audience at Oracle OpenWorld. I'm excited to let you know we have just pushed node-oracledb to GitHub.

Node.js is an upcoming, open source, cross-platform runtime environment for writing server-side and networking applications in JavaScript.

What is node-oracledb?

The node-oracledb driver connects to Oracle Database to make Node.js applications fast and functional. The node-oracledb 0.2 driver preview release already supports:

Node-oracledb is an open source project maintained by Oracle under the Apache 2.0 license. Contributions can be made under The Oracle Contributor Agreement

Where can I get node-oracledb?

To install node-oracledb, clone the repo, install the free Oracle Instant Client (or use a local Oracle Database such as the free Oracle Express Edition), and use npm to install from the cloned directory. The full instructions show how to create a local Node.js instance and covers more details. The driver is currently only on GitHub.

The driver uses Oracle client libraries - the same way Oracle Database drivers for other C based scripting languages do. This means it can take advantage of the significant feature set, engineering, and testing invested in those libraries. It also allows Node.js applications to talk to multiple different versions of the database with Oracle's standard client-to-server version compatibility.

The node-oracledb driver will build on Oracle Linux, OS X and Solaris. Our current testing has focused on Oracle Linux 6. Windows support shouldn't be difficult to add - sorry it's not there yet.

How do I use node-oracledb?

A simple query example is:

var oracledb = require('oracledb');

oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  function(err, connection)
  {
    if (err) { console.error(err); return; }
    connection.execute(
      "SELECT department_id, department_name "
    + "FROM departments "
    + "WHERE department_id < 70 "
    + "ORDER BY department_id",
      function(err, result)
      {
        if (err) { console.error(err); return; }
        console.log(result.rows);
      });
  });

The output, with Oracle's HR schema, is:

$ node select.js
[ [ 10, 'Administration' ],
  [ 20, 'Marketing' ],
  [ 30, 'Purchasing' ],
  [ 40, 'Human Resources' ],
  [ 50, 'Shipping' ],
  [ 60, 'IT' ] ]

The examples directory and API documentation contain more examples.

What is the future for node-oracledb?

We are actively working on adding features to node-oracledb including Windows platform support, LOB support, batch fetching / streaming of large query result sets, and DRCP support. We are also working on things like making it available on npmjs.com for standard npm installation, and making our test suite publishable.

We look forward to your constructive feedback so we can incorporate any fixes and "must-haves" into a 1.0 release soon.

I'm looking forward to sharing more with you as the driver progresses. My colleague Dan McGhan will also be evangelizing JavaScript and Oracle at jsao.io.

node-oracledb Links

Monday Jan 19, 2015

The Easiest Way to Install Oracle Database on Mac OS X

tl;dr Your OS X applications can connect to Oracle Database as if it was running natively on OS X if you simply run Oracle Database in a VirtualBox VM with port forwarding enabled (easy).

To work backwards through the installation process: in the Network window of the VirtualBox GUI, I enable a NAT Network adapter.

Then, under Networking -> Advanced -> Port Forwarding, I create a TCP rule with Host IP 127.0.0.1 and both the Host and Guest Port fields set to the port number that the Oracle network listener in the VM is using: the Oracle default is 1521. I leave the Guest IP field blank.

I click OK twice, dismissing the "The current port forwarding rules are not valid. None of the host or guest port values may be set to zero." dialog

Start the VM. If the DB is set to start during OS boot, you don't even need to log in.

Update: If you have Oracle Database 12c you can use the new EM Express console for DB management and monitoring. Just enable port forwarding for port 5500 and then browse (from OS X) to https://localhost:5500/em. See this article by Gerald Venzl.

The Client

Connecting to the database from OS X tools and clients uses the same forms of Oracle connect string as if the database was native on OS X. For example, using the "Easy Connect" hostname/service_name form, I can simply use 'localhost' as if the database was on OS X:

cjones@mac:~$ sqlplus cj/welcome@localhost/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 19 09:20:38 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

Here I was using SQL*Plus from the free, easy to install Instant Client bundle. Download the Oracle Instant Client Basic, SDK and SQL*Plus ZIP files. Unzip them, create the two symbolic links given in the install instructions, and set DYLD_LIBRARY_PATH to the Instant Client directory.

The Instant Client can be used, among other things, for building PHP, Python, Node.js and other language drivers. With each of these you would use exactly the same connect string to connect to the database.

Prebuilt VMs

There are prebuilt VMs with Oracle Database already installed, such as the Database App Development VM. Download and import it into VirtualBox.

The Database

It's easy to install your own Oracle Database "XE" Express Edition database for development. After the XE RPM is installed on Oracle Linux 5 and 6, run its simple configuration script to set up the administration passwords, the listener port (default is 1521), and whether to autostart during boot. Install XE by following the ten steps in Chapter 4 of The Underground PHP and Oracle Manual or check the official instructions.

Alternately you could install the Enterprise Edition for free ("only for the purpose of developing, testing, prototyping, and demonstrating" - read the click-through license). There are various scripts on the web to autostart at machine boot.

The OS

If you need an OS, Oracle Linux is free from our public yum server. Download the ISO, create a new VirtualBox VM and tell it where to find the ISO. Boot and follow the install prompts.

VirtualBox

VirtualBox is free from VirtualBox.org and runs on OS X, Windows, Linux and Solaris.

The only "trick" to using VirtualBox is to remember the magic cursor-releasing key, e.g. the Left Command Key on OS X. This key is useful if/when you haven't installed the VirtualBox "Guest Additions" into the VM and clicking into the VM window captures the cursor. The specific key combination is shown as a reminder on the bottom right of the containing VirtualBox VM window. Pressing it returns cursor control to the host OS. Luckily, once you install the "Guest Additions" the cursor is automatically released when you mouse out of the VM window.

How I Enable Autostarting of Oracle Database for Demonstrations and Development

Here are the steps I have been using to enable autostarting of Oracle Datbase Enterprise Edition 10g, 11g or 12c during boot time of Oracle Linux 5 and 6. I also use these steps with Oracle Linux 7. These steps are useful for the kinds of demonstration and development setups that I typically need.

These steps are not needed for Oracle XE, since its install will prompt whether to autostart the DB and will configure the system automatically.

Create a new service script

Create a file /etc/init.d/dbora using dborc

Set permissions on the script

# chmod 750 /etc/init.d/dbora

Tell Linux to autostart/stop the service

# chkconfig --add dbora
# chkconfig dbora on

Edit /etc/oratab

In /etc/oratab, change the autostart field from N to Y for any databases that you want autostarted.

Starting / Stopping the DB

The DB will start and stop at machine boot and shutdown.

Or it can be manually controlled with:

# service dbora start

and

# service dbora stop

Starting Oracle Database 12c Multitenant PDBs

To also start all pluggable databases when the container database starts, you can do:

connect / as sysdba

create or replace trigger sys.after_startup
   after startup on database
begin
   execute immediate 'alter pluggable database all open';
end after_startup;
/

Wednesday Oct 29, 2014

"PL/SQL: The Scripting Language Liberator" - video recording now available

Oracle University has released a video from Oracle OpenWorld of a great session by Steven Feuerstein and myself. We walked through a PHP application, showed some application tuning techniques for Oracle Database, and then looked at improving the use of Oracle Database features to aid performance and scalability, and also easily add features to the application.

The official blurb was:

PL/SQL: The Scripting Language Liberator: While scripting languages go in and out of favor, Oracle Database and PL/SQL persist, managing data and implementing business logic. This session walks through a web application to show how PL/SQL can be integrated for better logic encapsulation and performance; how Oracle's supplied packages can be used to enhance application functionality and reduce application complexity; and how to efficiently use scripting language connection and statement handling features to get better performance and scalability. Techniques shown in this session are applicable to mobile, web, or midtier applications written in languages such as JavaScript, Python, PHP, Perl, or Ruby on Rails. Using the right tool for the right job can be liberating.

The video is free for everyone. Lots of the other good content in the Oracle Learning Streams is available via subscription, if you're interested.

Friday Mar 14, 2014

Performance improvement for OCI_RETURN_LOBS in PHP OCI8 2.0.8

Reducing "round trips" to the database server is important for performance and ultimately for system scalability. A server round-trip is defined as the trip from PHP to the database server and back to PHP.

Reducing round trips makes everything more efficient: PHP is faster, there is less network overhead, the database doesn't have to do a context switch or do any processing for you. Applications have some control over round trips, for example by effectively using prefetching or by using an appropriate execute mode to minimize unneccessary rollbacks at the end of a script.

The bug filer of Bug 66875 noticed that PHP OCI8's implementation itself could reduce round trips if a particular LOB column meta data value was cached for the duration of a query instead of being re-requested for each row of the query result set.

So, now with OCI8 2.0.8, you should see a performance increase if you are doing multi-row queries involving LOB columns returned as OCI_RETURN_LOBS:

$s = oci_parse($c, "select mylobcol from mylobtab");
oci_execute($s);
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_LOBS)) !== false) {
    echo $row['MYLOBCOL'], "\n";
}

The bug filer tested the patch and added his performance improvement benchmark results to the bug report. The benefit in your environment will vary greatly with the network setup, schema, and how many LOB columns are queried. Test it out and let me know how the new version helps you.

There is no immediate change for LOBs fetched with OCI-Lob::read() and OCI-Lob::load(). This would require a more complex patch than I want to apply at this time. Queries that don't use LOBs are not affected in any way by the patch.

OCI8 2.0 is included in the forthcoming PHP 5.6 code base. For PHP 5.2 to PHP 5.5 you can install it from PECL. PHP 5.5 RPMs with PHP OCI8 2.0.8 are available from oss.oracle.com.

Finally, if your LOB queries return multiple rows, you might also like this tip to reduce PHP memory usage.

About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Follow:
Blaine Carter
Dan McGhan

Search

Archives
« August 2015
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