Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 7 hours 12 min ago

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!

Getting a C++11 compiler for Node 4, 5 and 6 on Oracle Linux 6

Sun, 2016-05-01 23:36

A newer compiler is needed on Oracle Linux 6 when you want to use add-ons like node-oracledb with Node 4 or later. This is because add-ons for those versions need to be built with a C++11 compatibile compiler. The default compiler on OL 6 doesn't have this support. OL 7 does have such a compiler, so these instructions are not needed for that version.

For OL 6 the easist way to get a new compiler is from the Software Collection Library (SCL). You enable the software collection yum channel, run a yum install command, and then the compiler is immediately available to use. Detailed installation SCL instructions are in the manual.

The steps below show how to install node-oracledb on Oracle Linux 6 for Node.js 4 or later.

Enabling the Software Collection Library

If you are using yum.oracle.com (formerly known as public-yum.oracle.com) then edit /etc/yum.repos.d/public-yum-ol6.repo and enable the ol6_software_collections channel:

  [ol6_software_collections]
  name=Software Collection Library release 1.2 packages for Oracle Linux 6 (x86_64)
  baseurl=http://yum.oracle.com/repo/OracleLinux/OL6/SoftwareCollections12/x86_64/
  gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
  gpgcheck=1
  enabled=1

If necessary, you can get the latest channel list from http://yum.oracle.com/public-yum-ol6.repo and merge any updates from it into your existing /etc/yum.repos.d/public-yum-ol6.repo file.

Alternatively, if your machine has a ULN support subscription, you can subscribe to the Oracle Software Collections 1.2 for Oracle Linux 6 channel in the Manage Subscription page on linux.oracle.com.

Installing the Updated Compiler

Once the channel is enabled, install the updated compiler with:

  yum install scl-utils devtoolset-3

This will install a number of packages that comprise the complete, updated tool set.

Installing node-oracledb

Installing node-oracledb on Node 4 (or later) is the same as in install instuctions, but using the new compiler. The Oracle Linux manual chapter Using the Software Collection Version of a Command shows various ways to enable the dev toolset.

In summary, to install node-oracledb on Node 4 or later using Oracle Linux 6, first install an Oracle client such as Instant Client. If you have anything except the Instant Client RPM packages, tell the installer where the libraries and header files are located, for example:

  export OCI_LIB_DIR=$HOME/instantclient
  export OCI_INC_DIR=$HOME/instantclient/sdk/include

If you are behind a firewall, set your proxy:

  export http_proxy=http://my.proxy.example.com:80/

In my development environments I often find some cleanup helps:

  which npm && rm -rf $(npm root)/oracledb $(npm root)/nan $HOME/.node-gyp $HOME/.npm \
        && npm cache clean

Now node-oracledb can be installed using the newer compiler:

  scl enable devtoolset-3 -- npm install oracledb
Using Node

Now you can use Node:

  $ node version.js 
  node.js version: v4.4.3
  node-oracledb version: 10900
  node-oracledb text format: 1.9.0
  oracle client library version: 1201000200
  oracle client library text format: 12.1.0.2.0
  oracle database version: 1201000200
  oracle database text format: 12.1.0.2.0

  $ cat /etc/oracle-release 
  oracle linux server release 6.7

node-oracledb 1.9.0-Dev Branch with Promises is on GitHub

Mon, 2016-04-18 23:22

Top features: Promise support

node-oracledb 1.9.0-Development is now available only as a development-only branch on GitHub. It adds Promise support and some other goodies mentioned below. The plan is to incorporate any user feedback, stabilize the features, improve documentation, improve test covereage, and run stress tests. Once this is all done, then a production release to npm will be made. Personally I think this will take 2-4 weeks, but it is totally dependent on what is uncovered by you, the user.

Since this is a development branch, features are subject to change. Use GitHub Issue #410 to ask design questions and discuss the branch.

Install by setting OCI_LIB_DIR and OCI_INC_DIR as normal, and running npm install oracle/node-oracledb.git#dev-1.9. Or install from GitHub by cloning the repository, checking out the dev-1.9 branch, setting OCI_LIB_DIR and OCI_INC_DIR, and install with npm install

Anyone is welcome to report test results, contribute new tests or update documentation (or code!) to help us get a production release out faster. You do need to have your OCA accepted first before we can look at any pull requests.

As you can see, most of the doc is ready, and there are a solid number of tests for new functionality. You can help us by testing your own favorite Promise library, as we are only planning on testing the default Promise implementation in Node 0.12, 4 and 5. Also check out how connections are handled as you may like a different style - let us know.

In node-oracledb 1.9-dev:

  • Promise support was added. All asynchronous functions can now return promises. The default Promise library is used for Node 0.12, 4 and 5. It can be easily overridden if you wish to incorporate your own implementation.

    The current implemention typically requires two promise chains. Let us know what you think. There are solutions that could be made. What do you want?

  • A new toQueryStream() method was added. It is for ResultSets. It lets REF CURSORS be transformed into Readable Streams. It can also be used to convert ResultSets from top-level queries to streams, however the existing connection.queryStream() method will probably be easier to use for these queries.

  • 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 whether the current Node Streams implementation really allows interruptions.

    Open question: Should _close() invoke pause() internally?

  • 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().

  • 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.

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

  • Some annoying, seemingly random crashes caused by connections being garbage collected while still in use should no longer occur. These were more common in smaller scripts that finished quickly, but could be seen in systems under load.

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.

Pages