Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 2 hours 1 min ago

node-oracledb 1.12: Working with LOBs as String and Buffer. Connection Pinging.

Wed, 2016-12-21 04:30

Node-oracledb 1.12, the Node.js add-on for Oracle Database, is now on NPM.

Top features: LOBs as JavaScript Strings and Buffers. Pool connection 'aliveness' checking.

The two main features of node-oracledb 1.12 add functionality that aids usability. You may not even notice one working in the background, silently re-establishing pooled connections after network outages. The other is a 'no brainer' that makes working with large objects (LOBs) easier. You'll probably use it and think nothing of it.

This release also contains a number of bug fixes and small changes. Check the CHANGELOG for all the details.

LOB Enhancements

Adding to the existing support for using CLOBs and BLOBs as Node.js Streams, now Strings and Buffers can be bound to CLOBs and BLOBs for IN, IN OUT and OUT binds. CLOBs in queries can also be returned directly as Strings. And streaming to and from Oracle's 'temporary LOBs' is supported. Also in this release, Writeable LOB Streams now conclude with a 'close' event.

The node-oracledb LOB documentation is worth reading. There are runnable examples in the examples directory.

To insert large data, just bind a Node.js String or Buffer to a CLOB or BLOB column, respectively. For example:

  var fs = require('fs');
  var str = fs.readFileSync('example.txt', 'utf 8');

  conn.execute(
    "INSERT INTO mylobs (id, myclobcol) VALUES (:idbv, :cbv)",
    { idbv: 1,
      cbv: str },  // type and direction are optional for IN binds
    function(err, result)
    {
      if (err)
	console.error(err.message);
      else
	console.log('CLOB inserted from example.txt');
      . . .

You can also bind Strings and Buffers to PL/SQL LOB parameters as IN, OUT and IN OUT binds.

To fetch CLOBs as Strings from queries, use fetchAsString or fetchInfo:

  // return all CLOBs as Strings
  oracledb.fetchAsString = [ oracledb.CLOB ];

  conn.execute(
    "SELECT mycol FROM mylobs WHERE id = 1",
    function(err, result) {
      if (err) { console.error(err.message); return; }
      if (result.rows.length === 0)
	console.error("No results");
      else {
	var clob = result.rows[0][0];
	console.log(clob);
      }
    });

Currently BLOBs in queries must be streamed; we didn't want to hold up the 1.12 release.

Use the right tool for the job: use Strings and Buffer for 'small' LOBs, and use Streams for everything else. There is a theoretical limit just under 1 GB in node-oracledb for manipulating LOBs as Strings or Buffers. Luckily you wouldn't want to be allocating that much memory in Node.js applications - it's better to stream. And Node.js itself doesn't cope well with large data. It can get unstable with Strings past the several hundred megabyte mark. (Note that if node-oracledb is linked with Oracle client 11g libraries, than the upper size of Strings and Buffers is just under the 64 KB mark: the moral is use Oracle client 12c libraries - you can still connect to Oracle Database 10.2, 11g and 12c).

For larger data you have multiple options for streaming. To insert into a table column, use the existing RETURNING INTO method where you get an Oracle LOB locator and use Node.js Streams functionality to put data into it. For fetching, use the existing LOB Streaming method and stream data out of the Lob.

If data is too big to bind as a String or Buffer to a PL/SQL parameter, you can create a 'temporary LOB' in node-oracledb, stream into it, and then bind it. This is new in node-oracledb 1.12

First, use the new connection.createLob() method to create a temporary LOB, here of type CLOB:

  conn.createLob(oracledb.CLOB, function(err, templob) {
    if (err) { . . . }
    // ... else use templob
  });

This creates an instance of a node-oracledb Lob class.

Once created, data can be inserted into the Lob. For example to read a text file:

  templob.on('error', function(err) { somecallback(err); });

  // The data was loaded into the temporary LOB, so use it
  templob.on('finish', function() { somecallback(null, templob); });

  // copies the text from 'example.txt' to the temporary LOB
  var inStream = fs.createReadStream('example.txt');
  inStream.on('error', function(err) { . . . });
  inStream.pipe(templob);

Now the LOB has been populated, it can be bound in somecallback() to a PL/SQL IN parameter:

  // For PROCEDURE lobs_in(p_id IN NUMBER, c_in IN CLOB)
  conn.execute(
    "BEGIN lobs_in (:id, :c); END",
    { id: 3,
      c: templob }, // type and direction are optional for IN binds
    function(err)
    {
      if (err) { return cb(err); }
      console.log("Call completed");
      return cb(null, conn, templob);
    });

When the LOB is no longer needed, it must be closed with lob.close():

  templob.close(function (err) {
    if (err)
      . . .
    else
      // success
  });
Connection Pool Enhancements

The other main feature in node-oracledb 1.12 is connection pool session 'aliveness' checking. This is enabled by default. You may never notice it in action but it will improve application behavior where networks are unstable, or database services become temporarily unavailable.

When pooled connections have been established to the database, but are idle in the connection pool not currently being used, there is a chance that a network outage will make those connections unusable. A common scenario is when a developer's laptop is closed for a while. Unless node-oracledb is linked with Oracle 12.2 client libraries, any node-oracledb pooled getConnection() call could return one of the unusable connections. For users of Oracle 11.2 or 12.1 client libraries, a new poolPingInterval setting will do a 'ping' to the database to check the connection is OK before returning that connection to the application. If it isn't OK, then another connection is returned.

There is the overhead of a 'round trip' to the database in doing a ping, so there could be some impact on scalability. But the ping setting is time based so there is no overhead on active pools. The ping will only happen when a connection has been idle in the pool for poolPingInterval seconds. If a connection has been recently used, it will be returned to the application without being checked. The default interval is 60 seconds. The feature can be configured to always-force the ping, to totally disable it, or to be whatever interval meets your performance and quality-of-service requirements. The value can be set when a pool is created, or turned on globally:

  oracledb.poolPingInterval = 60;

Applications should continue to detect and handle execution-time errors, since execution errors may occur, or a network outage may occur between getConnection() and execute() calls, or connections may have been in use and not released back to the pool when the network dropped, or the idle time may have been shorter than the ping interval setting and no ping performed. The new feature is great at improving the reliability of connections in unstable environments but is just one of the options and tools that can be used to provide a highly available application.

The ping feature is a no-op when node-oracledb is linked with Oracle 12.2 client libraries (independent of the database version at the other end of the connection), since those libraries have an always-enabled, lightweight connection check that removes the need for node-oracledb to do its own ping. This adds even more certainty that a connection will be valid at time of first use after a getConnection() call. Oracle 12.2 is currently available in Oracle Cloud.

The pool pinging documentation has more information.

Documentation Updates

If you haven't scanned the documentation for a while, take a look. Each release more and more tips and information gets added.

A couple of community contributions from Leigh Schrandt and Nick Heiner regarding the README were gratefully received. Thank you!

Testing

Testing of node-oracle is also something I wanted to mention. The test suite continues to grow, as you can see when you run it. We also have additional stress and loads tests - overall these extra test take days to complete. There are no guarantees, of course, but node-oracledb is more than solid.

Resources

Issues and questions about node-oracledb can be posted on GitHub. We value your input to help prioritize work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Finally, contributions to node-oracledb are more than welcome, see CONTRIBUTING.

node-oracledb 1.12.1-dev can fetch CLOBs as JavaScript String

Mon, 2016-12-19 17:12

A preview of node-oracledb 1.12.1-dev is available on GitHub and can be installed with:

npm install oracle/node-oracledb.git#v1.12.1-dev

Node-oracledb is the Node.js add-on for Oracle Database.

The 1.12.1-dev release introduces fetchAsStringsupport for CLOBs. Now, when CLOB columns are queried, they can bereturned directly as JavaScript Strings, without the need to useStreams. To test this in the dev release make sure node-oracledb islinked with Oracle 12c client libraries.

See the extensive manual for details and examples.

Also in this release is improved support for 'temporary LOBs'. Nowthey can be bound as IN OUT binds, (as well as IN and OUT!)

See my previous post for a brief intro to earlierchanges in this 1.12 series. The CHANGELOGhas all the updates. I'll blog the features in more detail when aproduction bundle is released to npmjs.com.

Resources

Issuesand questions about it can be posted on GitHub. Wevalue your input to help prioritize work on the add-on. Drop us aline!

Node-oracledb documentation is here.

node-oracledb 1.12.1-dev can fetch CLOBs as JavaScript String

Mon, 2016-12-19 17:12

A preview of node-oracledb 1.12.1-dev is available on GitHub and can be installed with:

  npm install oracle/node-oracledb.git#v1.12.1-dev

Node-oracledb is the Node.js add-on for Oracle Database.

The 1.12.1-dev release introduces fetchAsString support for CLOBs. Now, when CLOB columns are queried, they can be returned directly as JavaScript Strings, without the need to use Streams. To test this in the dev release make sure node-oracledb is linked with Oracle 12c client libraries.

See the extensive manual for details and examples.

Also in this release is improved support for 'temporary LOBs'. Now they can be bound as IN OUT binds, (as well as IN and OUT!)

See my previous post for a brief intro to earlier changes in this 1.12 series. The CHANGELOG has all the updates. I'll blog the features in more detail when a production bundle is released to npmjs.com.

Resources

Issues and questions about it can be posted on GitHub. We value your input to help prioritize work on the add-on. Drop us a line!

Node-oracledb documentation is here.

node-oracledb 1.12.0-dev available for preview

Mon, 2016-12-05 00:57

A preview of node-oracledb 1.12.0-dev is available on GitHub and can be installed with:

npm install oracle/node-oracledb.git#v1.12.0-dev

Node-oracledb is the Node.js add-on for Oracle Database.

The key things to look at are all the LOB enhancements - we want feedback. There are a couple more LOBenhancements we want to make before we go production, but I knowyou'll like the direction. Drop us a line via GitHub or email withyour comments.

This release also has a connection pool ping feature that will improve applicationavailability when transient network outages occur. It's enabled bydefault and will be transparent to most users, however it can be tunedor disabled if you have a special situation.

The CHANGELOG has all the other updates in this release. I'll blogall the features in more detail when a production bundle is released tonpmjs.com.

Resources

Issuesand questions about it can be posted on GitHub. Wevalue your input to help prioritize work on the add-on. Drop us aline!

Node-oracledb documentation is here.

node-oracledb 1.12.0-dev available for preview

Mon, 2016-12-05 00:57

A preview of node-oracledb 1.12.0-dev is available on GitHub and can be installed with:

  npm install oracle/node-oracledb.git#v1.12.0-dev

Node-oracledb is the Node.js add-on for Oracle Database.

The key things to look at are all the LOB enhancements - we want feedback. There are a couple more LOB enhancements we want to make before we go production, but I know you'll like the direction. Drop us a line via GitHub or email with your comments.

This release also has a connection pool ping feature that will improve application availability when transient network outages occur. It's enabled by default and will be transparent to most users, however it can be tuned or disabled if you have a special situation.

The CHANGELOG has all the other updates in this release. I'll blog all the features in more detail when a production bundle is released to npmjs.com.

Resources

Issues and questions about it can be posted on GitHub. We value your input to help prioritize work on the add-on. Drop us a line!

Node-oracledb documentation is here.

Fast Generation of CSV and JSON from Oracle Database

Thu, 2016-11-10 01:38

Want a fast way to spool CSV or JSON from Oracle Database? Checkout the new SQL*Plus 12.2 SET MARKUP CSV option and the new Oracle Database12.2 JSON_OBJECT operator.

You can try Oracle Database 12.2 now using Oracle Cloud DatabaseService.

Fast, Easy CSV with SQL*Plus and Oracle Database

First, let's see CSV ("comma separated values") output in SQL*Plus12.2. Start with this script, t.sql:

set feedback off select department_id, department_name from departments where department_id < 110; exit

Executed traditionally you get formatted output:

SQL> @t.sql DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------

10 Administration

20 Marketing

30 Purchasing

40 Human Resources

50 Shipping

60 IT

70 Public Relations

80 Sales

90 Executive

100 Finance

Running it with the new CSV mode:

SQL> set markup csv on SQL> @t.sql "DEPARTMENT_ID","DEPARTMENT_NAME" 10,"Administration" 20,"Marketing" 30,"Purchasing" 40,"Human Resources" 50,"Shipping" 60,"IT" 70,"Public Relations" 80,"Sales" 90,"Executive" 100,"Finance"

Simple!

The full CSV syntax is:

SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

You can see the delimiter can be changed from a comma, andquoting of fields can be disabled.

The SET MARKUP option can also be enabled from thecommand line with the -m option:

$ sqlplus -s -m 'csv on' cj@pdb1 @t.sql "DEPARTMENT_ID","DEPARTMENT_NAME" 10,"Administration" 20,"Marketing" 30,"Purchasing" 40,"Human Resources" 50,"Shipping" 60,"IT" 70,"Public Relations" 80,"Sales" 90,"Executive" 100,"Finance"(Pro tip: the -s silent option may hide the password prompt on some platforms making SQL*Plus appear to hang! Enter the password and all will be well.)

CSV mode bypasses the traditional SQL*Plus pagination routines,making output faster to generate. And using the -m 'csvon' option additionally invokes a faster I/O subsystem, andalso automatically sets some SET options to the values ofanother new option sqlplus -f. Two of the changes are increasingSET ARRAYSIZE to 100 and SET ROWPRETCH to2.

Increasing SET ARRAYSIZE allows larger amounts of datato be returned in each low level request to the database, thusimproving overall efficiency. Having SET ROWPRETCH to 2reduces the database 'round trips' required between SQL*Plus and thedatabase for queries that return only single rows. You should adjustthe values of these, and other, settings to suit your datasize and performance goals.

CSV mode can be used when connecting SQL*Plus 12.2 to OracleDatabase 12.2 or earlier.

Fast, Easy JSON with SQL*Plus and Oracle Database

The Oracle Database 12.2 JSON_OBJECT function is a great way to convert relational tableoutput into JSON.

Combined with SQL*Plus 12.2's efficient CSV output - and withquoting of columns disabled - you can spool JSON very easily.

Here's a SQL*Plus script t2.sql to return JSON output:

set heading off set feedback off select json_object ('deptId' is d.department_id, 'name' is d.department_name) department from departments d where department_id < 110; exit

Running it generates the desired JSON output:

$ sqlplus -s -m 'csv on quote off' cj@pdb1 @t2.sql {"deptId":10,"name":"Administration"} {"deptId":20,"name":"Marketing"} {"deptId":30,"name":"Purchasing"} {"deptId":40,"name":"Human Resources"} {"deptId":50,"name":"Shipping"} {"deptId":60,"name":"IT"} {"deptId":70,"name":"Public Relations"} {"deptId":80,"name":"Sales"} {"deptId":90,"name":"Executive"} {"deptId":100,"name":"Finance"}Summary

SQL*Plus 12.2 and Oracle Database 12.2 allow efficient and easyaccess to CSV and JSON data. If you don't yet have 12.2, or you wantto create web services and do more with the data, check out Oracle ORDS or roll your own solution using the node-oracledbdriver for Node.js.

Fast Generation of CSV and JSON from Oracle Database

Thu, 2016-11-10 01:38

Want a fast way to spool CSV or JSON from Oracle Database? Check out the new SQL*Plus 12.2 SET MARKUP CSV option and the new Oracle Database 12.2 JSON_OBJECT operator.

You can try Oracle Database 12.2 now using Oracle Cloud Database Service.

Fast, Easy CSV with SQL*Plus and Oracle Database

First, let's see CSV ("comma separated values") output in SQL*Plus 12.2. Start with this script, t.sql:

  set feedback off

  select department_id, department_name from departments where department_id < 110;
  exit

Executed traditionally you get formatted output:

  SQL> @t.sql

  DEPARTMENT_ID DEPARTMENT_NAME
  ------------- ------------------------------
	     10 Administration
	     20 Marketing
	     30 Purchasing
	     40 Human Resources
	     50 Shipping
	     60 IT
	     70 Public Relations
	     80 Sales
	     90 Executive
	    100 Finance

Running it with the new CSV mode:

  SQL> set markup csv on
  SQL> @t.sql

  "DEPARTMENT_ID","DEPARTMENT_NAME"
  10,"Administration"
  20,"Marketing"
  30,"Purchasing"
  40,"Human Resources"
  50,"Shipping"
  60,"IT"
  70,"Public Relations"
  80,"Sales"
  90,"Executive"
  100,"Finance"

Simple!

The full CSV syntax is:

  SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

You can see the delimiter can be changed from a comma, and quoting of fields can be disabled.

The SET MARKUP option can also be enabled from the command line with the -m option:

  $ sqlplus -s -m 'csv on' cj@pdb1 @t.sql

  "DEPARTMENT_ID","DEPARTMENT_NAME"
  10,"Administration"
  20,"Marketing"
  30,"Purchasing"
  40,"Human Resources"
  50,"Shipping"
  60,"IT"
  70,"Public Relations"
  80,"Sales"
  90,"Executive"
  100,"Finance"
(Pro tip: the -s silent option may hide the password prompt on some platforms making SQL*Plus appear to hang! Enter the password and all will be well.)

CSV mode bypasses the traditional SQL*Plus pagination routines, making output faster to generate. And using the -m 'csv on' option additionally invokes a faster I/O subsystem, and also automatically sets some SET options to the values of another new option sqlplus -f. Two of the changes are increasing SET ARRAYSIZE to 100 and SET ROWPRETCH to 2.

Increasing SET ARRAYSIZE allows larger amounts of data to be returned in each low level request to the database, thus improving overall efficiency. Having SET ROWPRETCH to 2 reduces the database 'round trips' required between SQL*Plus and the database for queries that return only single rows. You should adjust the values of these, and other, settings to suit your data size and performance goals.

CSV mode can be used when connecting SQL*Plus 12.2 to Oracle Database 12.2 or earlier.

Fast, Easy JSON with SQL*Plus and Oracle Database

The Oracle Database 12.2 JSON_OBJECT function is a great way to convert relational table output into JSON.

Combined with SQL*Plus 12.2's efficient CSV output - and with quoting of columns disabled - you can spool JSON very easily.

Here's a SQL*Plus script t2.sql to return JSON output:

  set heading off
  set feedback off

  select json_object ('deptId' is d.department_id, 'name' is d.department_name) department 
  from departments d 
  where department_id < 110;

  exit

Running it generates the desired JSON output:

  $ sqlplus -s -m 'csv on quote off' cj@pdb1 @t2.sql
  {"deptId":10,"name":"Administration"}
  {"deptId":20,"name":"Marketing"}
  {"deptId":30,"name":"Purchasing"}
  {"deptId":40,"name":"Human Resources"}
  {"deptId":50,"name":"Shipping"}
  {"deptId":60,"name":"IT"}
  {"deptId":70,"name":"Public Relations"}
  {"deptId":80,"name":"Sales"}
  {"deptId":90,"name":"Executive"}
  {"deptId":100,"name":"Finance"}
Summary

SQL*Plus 12.2 and Oracle Database 12.2 allow efficient and easy access to CSV and JSON data. If you don't yet have 12.2, or you want to create web services and do more with the data, check out Oracle ORDS or roll your own solution using the node-oracledb driver for Node.js.

Application Development at Oracle OpenWorld, San Francisco, September 2016

Thu, 2016-08-25 03:11

Well, there is certainly a lot going on at Oracle OpenWorld thisSeptember. You can browse the session catalog for interesting talks. Update If you need just one link, use our "Focus on Document" to guide your session choice.

Here are a few highlights in my area:

That's some great content there.

The "Meet the Experts" session is the interactive session where youget to hear from, and ask questions to, our key developers andmanagers in the Scripting Language and .NET area. If you're shy, youdon't have to speak - just come and learn.

We'll also have a demo booth open on the exhibition floor so youcan come and chat. (Its location is yet to be announced).

I really hope to see you during the 2016 conference.

Application Development at Oracle OpenWorld, San Francisco, September 2016

Thu, 2016-08-25 03:11

Well, there is certainly a lot going on at Oracle OpenWorld this September. You can browse the session catalog for interesting talks.

Here are a few highlights in my area:

That's some great content there.

The "Meet the Experts" session is the interactive session where you get to hear from, and ask questions to, our key developers and managers in the Scripting Language and .NET area. If you're shy, you don't have to speak - just come and learn.

We'll also have a demo booth open on the exhibition floor so you can come and chat. (Its location is yet to be announced).

I really hope to see you during the 2016 conference.

Node-oracledb 1.11 Released to NPM

Fri, 2016-08-19 02:09

We've pushed out a release of node-oracledb toGitHub and NPM.

Top feature: Connection Pool Cache

The node-oracledb driver connects Node.js to Oracle Database for fastand functional applications.

The changes in node-oracledb 1.11 are:
  • Added a connection pool cache feature allowing pools to be given a string alias at creation. This makes pools easier to use.

    Pools are typically created in one file and later used in other files. Giving a pool a string alias allows these other modules to access the pool by knowing only its alias name.

    When pools are created with oracledb.createPool(), the pool attributes can now optionally contain a new poolAlias string.

    var hrPoolPromise = oracledb.createPool({ poolAlias: 'pool1', users: 'hr', password: 'welcome', connectString: 'localhost/orcl'});

    Pools can be retrieved from the cache using a new oracledb.getPool() method, and then used as before to get connections. Multiple pools, each with a different alias, can be used.

    Interestingly, oracledb.getConnection() has been enhanced to take a pool alias specifying which pool to return a connection from. This bypasses the need to calloracledb.getPool().

    And there's more! The first pool (in a normal code flow) created without a poolAlias attribute is given the alias "default". Connections can be retrieved from this pool by using oracledb.getConnection() without passing an alias at all. This means applications that create only a single pool can simply use oracledb.getConnection() anywhere to get a connection from the pool.

    More information and examples are in the documentation. It is worth checking this since oracledb.getConnection() now has several different behaviors, depending on the use of aliases (and whether callbacks or promises are used).

    We'd recommend using aliases all the time if you create more than one pool - and want to access them via aliases. Using aliases is optional. Don't mix un-aliased and aliased pools unless you want to confuse yourself.

  • Improved the bootstrap error message when the node-oracledb binary cannot be loaded.

  • Fixed memory leaks with DATE and TIMESTAMP bind values.

  • Fixed external authentication which broke in 1.10.

  • Fixed metadata scale and precision values on AIX.

  • A couple of other internal improvements are shown in the CHANGELOG.

Resources

Issues and questions about node-oracledb can be posted on GitHub.Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

Node-oracledb 1.11 Released to NPM

Fri, 2016-08-19 02:09

We've pushed out a release of node-oracledb to GitHub and NPM.

Top feature: Connection Pool Cache

The node-oracledb driver connects Node.js to Oracle Database for fast and functional applications.

The changes in node-oracledb 1.11 are:
  • Added a connection pool cache feature allowing pools to be given a string alias at creation. This makes pools easier to use.

    Pools are typically created in one file and later used in other files. Giving a pool a string alias allows these other modules to access the pool by knowing only its alias name.

    When pools are created with oracledb.createPool(), the pool attributes can now optionally contain a new poolAlias string.

    var hrPoolPromise = oracledb.createPool({
      poolAlias: 'pool1',
      users: 'hr',
      password: 'welcome',
      connectString: 'localhost/orcl'
    });

    Pools can be retrieved from the cache using a new oracledb.getPool() method, and then used as before to get connections. Multiple pools, each with a different alias, can be used.

    Interestingly, oracledb.getConnection() has been enhanced to take a pool alias specifying which pool to return a connection from. This bypasses the need to call oracledb.getPool().

    And there's more! The first pool (in a normal code flow) created without a poolAlias attribute is given the alias "default". Connections can be retrieved from this pool by using oracledb.getConnection() without passing an alias at all. This means applications that create only a single pool can simply use oracledb.getConnection() anywhere to get a connection from the pool.

    More information and examples are in the documentation. It is worth checking this since oracledb.getConnection() now has several different behaviors, depending on the use of aliases (and whether callbacks or promises are used).

    We'd recommend using aliases all the time if you create more than one pool - and want to access them via aliases. Using aliases is optional. Don't mix un-aliased and aliased pools unless you want to confuse yourself.

  • Improved the bootstrap error message when the node-oracledb binary cannot be loaded.

  • Fixed memory leaks with DATE and TIMESTAMP bind values.

  • Fixed external authentication which broke in 1.10.

  • Fixed metadata scale and precision values on AIX.

  • A couple of other internal improvements are shown in the CHANGELOG.

Resources

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

PHP OCI8 2.1.2 and OCI8 2.0.12 released on PECL

Thu, 2016-08-18 20:04

PHP OCI8 2.1.2 (for PHP 7) and OCI8 2.0.12 (for PHP 5.2+) have been uploaded to PECL. The OCI8 extension allows PHP applications to connect to Oracle Database.

Use pecl install oci8 to install PHP OCI8 2.1 for PHP 7.

Use pecl install oci8-2.0.12 to install PHP OCI8 2.0 for PHP 5.2 - PHP 5.6. Note: PHP 5.6 is the oldest release which the PHP community is maintaining.

As well as being available from PECL, PHP OCI8 is also included inthe base PHP bundles.OCI8 2.0.12 will be included in PHP 5.6.26. OCI8 2.1.2 will beincluded in PHP 7.0.11 and PHP 7.1.

The changes are:

  • Fixed an invalid handle error with Implicit Result Sets. (OCI8 2.0.12 and 2.1.2)

  • Fixed bug #72524 (Binding null values triggers ORA-24816 error) (OCI8 2.1).

    This necessarily reverts a change in PHP 7 (OCI8 2.1) back to match PHP 5's (OCI8 2.0) behavior. If you had PHP 7 code that was not specifying a size for OUT binds, then you will need to modify your code. Passing a maxlength to oci_bind_by_name() for OUT binds remains the documented and recommended best practice.

  • Several internal code tidy ups (OCI8 2.1).

More about PHP and OCI8 can be found in the The Underground PHP and Oracle Manual and on the Oracle Technology Network.

PHP OCI8 2.1.2 and OCI8 2.0.12 released on PECL

Thu, 2016-08-18 20:04

PHP OCI8 2.1.2 (for PHP 7) and OCI8 2.0.12 (for PHP 5.2+) have been uploaded to PECL. The OCI8 extension allows PHP applications to connect to Oracle Database.

Use pecl install oci8 to install PHP OCI8 2.1 for PHP 7.

Use pecl install oci8-2.0.12 to install PHP OCI8 2.0 for PHP 5.2 - PHP 5.6. Note: PHP 5.6 is the oldest release which the PHP community is maintaining.

As well as being available from PECL, PHP OCI8 is also included in the base PHP bundles. OCI8 2.0.12 will be included in PHP 5.6.26. OCI8 2.1.2 will be included in PHP 7.0.11 and PHP 7.1.

The changes are:

  • Fixed an invalid handle error with Implicit Result Sets. (OCI8 2.0.12 and 2.1.2)

  • Fixed bug #72524 (Binding null values triggers ORA-24816 error) (OCI8 2.1).

    This necessarily reverts a change in PHP 7 (OCI8 2.1) back to match PHP 5's (OCI8 2.0) behavior. If you had PHP 7 code that was not specifying a size for OUT binds, then you will need to modify your code. Passing a maxlength to oci_bind_by_name() for OUT binds remains the documented and recommended best practice.

  • Several internal code tidy ups (OCI8 2.1).

More about PHP and OCI8 can be found in the The Underground PHP and Oracle Manual and on the Oracle Technology Network.

Node-oracledb 1.10.1 Released to NPM

Wed, 2016-07-20 21:10

We've pushed out a patch release of node-oracledb toGitHub and NPM. It has a bind fix that didn't land in time for1.10.0. A memory leak regression with REF CURSORs was also fixed, aswas a pre-existing memory leak in the unexpected case of a REF CURSORfailure.

The changes in node-oracledb 1.10.1 are:
  • Fixed a bug that prevented a null value being passed from JavaScript into an IN OUT bind.

  • Fixed a memory leak introduced in 1.10 with REF CURSORs.

  • Fixed an existing memory leak problem if an error happens when fetching REF CURSORs.

  • Added a mocha configuration file for the test suite. The test suite README has updated instructions on how to add tests.

    As a side effect, the tests now run in numeric order.

A couple of other internal improvements are shown in the CHANGELOG.

Resources

Issues and questions about node-oracledb can be posted on GitHub.Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

Node-oracledb 1.10.1 Released to NPM

Wed, 2016-07-20 21:10

We've pushed out a patch release of node-oracledb to GitHub and NPM. It has a bind fix that didn't land in time for 1.10.0. A memory leak regression with REF CURSORs was also fixed, as was a pre-existing memory leak in the unexpected case of a REF CURSOR failure.

The changes in node-oracledb 1.10.1 are:
  • Fixed a bug that prevented a null value being passed from JavaScript into an IN OUT bind.

  • Fixed a memory leak introduced in 1.10 with REF CURSORs.

  • Fixed an existing memory leak problem if an error happens when fetching REF CURSORs.

  • Added a mocha configuration file for the test suite. The test suite README has updated instructions on how to add tests.

    As a side effect, the tests now run in numeric order.

A couple of other internal improvements are shown in the CHANGELOG.

Resources

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Node-oracledb installation instructions are here.

Node-oracledb API and user documentation is here.

Node-oracledb 1.10 has Enhanced Metadata

Fri, 2016-07-08 03:06

Top feature: Enhanced Metadata

The changes in node-oracledb 1.10 are:
  • Enhanced query metadata thanks to a Pull Request from Leonardo. He kindly allowed us to take over and fine tune the implementation.

    Additional metadata for query and REF CURSOR columns is available in the metaData object when the new booleanoracledb.extendedMetaData attribute or corresponding execute() option attribute extendedMetaData are true.

    For example, if the DEPARTMENTS table is like:

    SQL> desc departments Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

    Then a query in node-oracledb would give extended metadata:

    [ { name: 'DEPARTMENT_ID', fetchType: 2002, dbType: 2, precision: 4, scale: 0, nullable: false }, { name: 'DEPARTMENT_NAME', fetchType: 2001, dbType: 1, byteSize: 30, nullable: false }, { name: 'MANAGER_ID', fetchType: 2002, dbType: 2, precision: 6, scale: 0, nullable: true }, { name: 'LOCATION_ID', fetchType: 2002, dbType: 2, precision: 4, scale: 0, nullable: true } ]

    You can see that the available attributes vary with the database type. The attributes are described in the metaData documentation.

    The commonly used column name is always available inmetaData regardless of the value ofextendedMetaData. This is consistent with previous versions.

    The metadata dbType and fetchType attributes numbers are described in new DB_TYPE_* constants and the existing node-oracledb type constants, respectively. Your code should use these constants when checking metadata types.

    Why did we make the extra metadata optional and off by default? Why do the types use numbers instead of strings? We had a lot of debate about common use cases, out-of-box experience, performance etc. and this is the way the cookie crumbled.

    I know this enhancement will make your applications easier to maintain and more powerful.

  • Fixed an issue preventing the garbage collector cleaning up when a query with LOBs is executed but LOB data isn't actually streamed.

  • Report an error earlier when a named bind object is used in a bind-by-position context. A new error NJS-044 is returned. Previously errors like ORA-06502 were given since the expected attributes were not found and bind settings ended up as defaults. You can still use unnamed objects for bind-by-position binds like:

    var sql = "begin myproc(:1, :2, :3); end;";var binds = [ id, name, { type: oracledb.STRING, dir: oracledb.BIND_OUT } ];

    Here the third array element is an unnamed object.

  • Fixed a bug where an error event could have been emitted on a QueryStream instance prior to the underlying ResultSet having been closed. This would cause problems if the user tried to close the connection in the error event handler as the ResultSet could have prevented it.

  • Fixed a bug where the public close method was invoked on the ResultSet instance that underlies the QueryStream instance if an error occurred during a call to getRows. The public method would have thrown an error had the QueryStream instance been created from a ResultSet instance via the toQueryStream method. Now the call to the C layer close method is invoked directly.

  • Updated Pool._logStats to throw an error instead of printing to the console if the pool is not valid.

  • Added GitHub Issue and Pull Request templates.

  • Updated installation instructions for OS X using the new Instant Client 12.1 release.

  • Added installation instructions for AIX and Solaris x64.

  • Some enhancements were made to the underlying DPI data access layer. These were developed in conjuction with a non- node-oracledb consumer of DPI, but a couple of changes lay groundwork for potential, user-visible, node-oracledb enhancements:

    • Allow SYSDBA connections

    • Allow session tagging

    • Allow the character set and national character set to be specified via parameters to the DPI layer

    • Support heterogeneous pools (in addition to existing homogeneous pools)

    To reiterate, these are not exposed to node-oracledb.

Resources

Issues and questions about node-oracledb can be posted on GitHub.Your input helps us schedule work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb 1.10 has Enhanced Metadata

Fri, 2016-07-08 03:06

Top feature: Enhanced Metadata

The changes in node-oracledb 1.10 are:
  • Enhanced query metadata thanks to a Pull Request from Leonardo. He kindly allowed us to take over and fine tune the implementation.

    Additional metadata for query and REF CURSOR columns is available in the metaData object when the new boolean oracledb.extendedMetaData attribute or corresponding execute() option attribute extendedMetaData are true.

    For example, if the DEPARTMENTS table is like:

    SQL> desc departments
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DEPARTMENT_ID                             NOT NULL NUMBER(4)
     DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
     MANAGER_ID                                         NUMBER(6)
     LOCATION_ID                                        NUMBER(4)
    

    Then a query in node-oracledb would give extended metadata:

    [ { name: 'DEPARTMENT_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 4,
           scale: 0,
           nullable: false },
         { name: 'DEPARTMENT_NAME',
           fetchType: 2001,
           dbType: 1,
           byteSize: 30,
           nullable: false },
         { name: 'MANAGER_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 6,
           scale: 0,
           nullable: true },
         { name: 'LOCATION_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 4,
           scale: 0,
           nullable: true } ]

    You can see that the available attributes vary with the database type. The attributes are described in the metaData documentation.

    The commonly used column name is always available in metaData regardless of the value of extendedMetaData. This is consistent with previous versions.

    The metadata dbType and fetchType attributes numbers are described in new DB_TYPE_* constants and the existing node-oracledb type constants, respectively. Your code should use these constants when checking metadata types.

    Why did we make the extra metadata optional and off by default? Why do the types use numbers instead of strings? We had a lot of debate about common use cases, out-of-box experience, performance etc. and this is the way the cookie crumbled.

    I know this enhancement will make your applications easier to maintain and more powerful.

  • Fixed an issue preventing the garbage collector cleaning up when a query with LOBs is executed but LOB data isn't actually streamed.

  • Report an error earlier when a named bind object is used in a bind-by-position context. A new error NJS-044 is returned. Previously errors like ORA-06502 were given since the expected attributes were not found and bind settings ended up as defaults. You can still use unnamed objects for bind-by-position binds like:

    var sql = "begin myproc(:1, :2, :3); end;";
    var binds = [ id, name, { type: oracledb.STRING, dir: oracledb.BIND_OUT } ];
    

    Here the third array element is an unnamed object.

  • Fixed a bug where an error event could have been emitted on a QueryStream instance prior to the underlying ResultSet having been closed. This would cause problems if the user tried to close the connection in the error event handler as the ResultSet could have prevented it.

  • Fixed a bug where the public close method was invoked on the ResultSet instance that underlies the QueryStream instance if an error occurred during a call to getRows. The public method would have thrown an error had the QueryStream instance been created from a ResultSet instance via the toQueryStream method. Now the call to the C layer close method is invoked directly.

  • Updated Pool._logStats to throw an error instead of printing to the console if the pool is not valid.

  • Added GitHub Issue and Pull Request templates.

  • Updated installation instructions for OS X using the new Instant Client 12.1 release.

  • Added installation instructions for AIX and Solaris x64.

  • Some enhancements were made to the underlying DPI data access layer. These were developed in conjuction with a non- node-oracledb consumer of DPI, but a couple of changes lay groundwork for potential, user-visible, node-oracledb enhancements:

    • Allow SYSDBA connections

    • Allow session tagging

    • Allow the character set and national character set to be specified via parameters to the DPI layer

    • Support heterogeneous pools (in addition to existing homogeneous pools)

    To reiterate, these are not exposed to node-oracledb.

Resources

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Using Oracle on OS X? Instant Client 12.1 is here

Sun, 2016-05-29 07:45

Oracle Instant Client 12.1 for OS X was just released and is now available for free download from OTN for 32-bit and 64-bit applications. Update: the bundles were re-released 14 June 2016 with a connectivity fix.

Instant Client provides libraries and tools for connecting to Oracle Database. Among other uses, languages such as C, Python, PHP, Ruby, Perl and Node.js can use Instant Client for database connectivity.

In addition to having Oracle 12.1 client features like auto-tuning, new in this release is an ODBC driver.

The install instructions have been updated to reflect the resolution of the linking issues caused by the OS X El Capitan changes with SIP to ignore DYLD_LIBRARY_PATH in sub processes. The ~/lib location required for Instant Client 11.2 on El Capitan is no longer needed with Instant Client 12.1. Note if you are creating your own apps, you should link with -rpath.

This release of Instant Client supports Mavericks, Yosemite, and El Capitan. Applications can connect to Oracle Database 10.2 or more recent. You should continue using the older 11.2 client if you need to connect to Oracle Database 9.2.

Update: Official installation doc and release notes are now on the doc portal: Oracle Database Online Documentation 12c Release 1 (12.1).

Questions and comments can be posted to the OTN forum for whichever component or tool you are using. General questions about Instant Client are best posted to the OCI Forum.

If you are interested in running Oracle Database itself on OS X, see my earlier post The Easiest Way to Install Oracle Database on Mac OS X.

Node-oracledb 1.9.1 with Promises and Node 6 Support is on NPM

Wed, 2016-05-18 05:14

Top features: Promise support. Node 6 support.

The new node-oracledb 1.9.1 release adds Promise support, it now supports the new Node 6 (as well as previous releases), and it has some welcome stability fixes. Other nice goodies are mentioned below. Thanks to everyone who gave feedback on our 1.9.0 development branch - we couldn't have done it without you.

node-oracledb 1.9.1 is now available on NPM. Installation instructions are here. Documentation is here.

The changes in node-oracledb 1.9.1 since 1.8
  • Promise support was added. All asynchronous functions can now optionally return Promises. When asynchronous functions are passed with a callback function parameter, the familiar callback flow is used. If the callback parameter is omitted, then a Promise is returned. There are some examples in the examples directory, see promises.js and webapppromises.js.

    Node Promises allow a programming paradigm that many Node users find comfortable. Some users had implemented their own Promise wrappers for node-oracledb; a few had even been published on NPM. Now the official implementation makes Promises available to everyone.

    The default Promise library is used for Node 0.12, 4, 5 and 6. It can be easily overridden if you wish to incorporate your own implementation. This is also useful for Node 0.10 where there is no native library.

  • A new toQueryStream() method was added for ResultSets. It lets REF CURSORS be fetched via Readable Streams. See the example refcursortoquerystream.js

    It can also be used with ResultSets from top-level queries, allowing them to be converted to streams. However the existing connection.queryStream() method will probably be easier to use in this scenario.

  • Aliases pool.close() and connection.close() have been added for pool.terminate() and connection.release() respectively. This should make it easier to remember which method to use for releasing a connection, terminating a connection pool, or closing a ResultSet: you can just use close().

  • An experimental query Stream _close() method was added. It allows query streams to be closed without needing to fetch all the data. It is marked experimental because there isn't good information about how safe interrupting the current Node Streams implementation is.

    Calling _close() invokes pause(), if necessary. On Node 0.10 you may get a pause event even if you had previously paused the stream. This is because Node 0.10 doesn't support the isPaused() call.

  • Upgraded to NAN 2.3 for Node 6. This removes the deprecation warnings that Node 6 was generating with the older NAN 2.2.

    Older versions of Node can still be used, of course.

  • Mitigated some annoying, seemingly random crashes if JavaScript objects were garbage collected too early. While many well programmed apps naturally hold the relevant JavaScript objects until no longer required, other apps don't. Premature garbage collection could affect these latter scripts if they finished quickly or when under load.

    A similar change was made to Lob buffers used during LOB insertion.

  • Memory 'improvements':

    • Fixed some memory leaks when using ResultSets.

    • Fixed a memory leak with the Pool queue timer map.

    • Fixed memory release logic when querying LOBs and an error occurs.

  • Removed an extra call to getRows() made by queryStream() at end-of-fetch.

  • Error changes:

    • Some method parameter validation checks, such as the number or types of parameters, will now throw errors synchronously instead of returning errors via the callback.

    • Improved validation for fetchInfo usage.

    • Increased the internal buffer size for Oracle Database error messages.

    • Altered some node-oracledb NJS-xyz error message text for consistency.

  • The test suite is no longer automatically installed when installing with npm install oracledb from NPM. The test suite was (and will be) getting bigger and bigger. It is not being used by most people, so installing it automatically is wasteful.

    You can still run the tests using a GitHub clone. The updated test README has instructions for this. To allow tests to be moved and run anywhere, we left the tests' require('oracledb') calls without a path prefix so you may need to set NODE_PATH as described.

  • Fixed a symbol name redefinition warning for DATA_BLOB when compiling on Windows.

Overall node-oracledb 1.9.1 is a significant milestone with the addition of Promise support, the support for Node 6, and the general resiliency changes. I'm very happy with this release and would encourage upgrading to it.

What's Next?

What's next? There are still lots of enhancements for node-oracledb on the wish list. Please let us know your priorities.

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

Emoji? Look at the Oracle New Gen Developers

Fri, 2016-05-13 05:36

Forget emoji, look at the Oracle New Gen Developers!

Pages