Monday Jun 22, 2015

Python cx_Oracle 5.2 driver for Oracle Database has been released

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

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

The new code features are:

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

The complete release notes are here.

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

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

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

Resources:

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

Wednesday Jun 17, 2015

Scripting Languages & Oracle: Blogs to Follow

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

Tuesday May 26, 2015

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

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

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

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

Friday May 08, 2015

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

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

  • Changed the isAutoCommit attribute name to autoCommit.

    Changed the isExternalAuth attribute name to externalAuth.

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

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

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

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

  • Rectified the error message for invalid type properties.

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

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

Tuesday Apr 14, 2015

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

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

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

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

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

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

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

To join the webinar, go to Webex

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

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

Saturday Mar 28, 2015

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

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

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

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

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

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

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

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

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

Friday Mar 13, 2015

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

Just a few small changes in this update of the Node.js driver for Oracle Database.

  • Support for External authentication was added This closes Issue #15.

  • The isAutoCommit flags now works with query execution. This is useful in cases where multiple DML statements are executed followed by a SELECT statement. This can be used to avoid a round trip to the database that an explicit call to commit() would add.

  • Added AIX build support to package.json. Thanks to Hannes Prirschl for submitting a pull request.

  • Errors messages when using properties that are out of range have been improved

  • Numerous API doc updates. Thanks to Greg Huang, BuiltInParris, mello151, and others for instigating some of these changes.

  • Fixed a bug: When terminate() of a connection pool fails because connections have not yet been closed, subsequent use of release() to close those connections no longer gives an error "ORA-24550: Signal Received".

  • Thanks go to krishnanm86 for a code cleanup pull request.

Bigger features (e.g. LOBS and RETURNING INTO) that I know you really, really want are still being worked on - have patience!

Thursday Feb 19, 2015

Running Carsten Czarski's node-oracledb WebSocket Example

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

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

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

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

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

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

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

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

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

    $ npm start
    

    The server will start:

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

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

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

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

You can follow Carsten at @cczarski.

Monday Feb 16, 2015

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

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

Updates for node-oracledb 0.3.1

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

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

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

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

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

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

  • Fixed setting the isAutoCommit property.

  • Fixed a crash using pooled connections on Windows.

  • Fixed a crash querying object types.

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

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

  • Updated version to 0.3.1 (surprise!)

Thursday Jan 22, 2015

A Personal Victory: Oracle Database Sample Schemas are on GitHub

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

This new repository installs these six sample schemas:

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

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

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

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

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

Tuesday Jan 20, 2015

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

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

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

What is node-oracledb?

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

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

Where can I get node-oracledb?

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

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

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

How do I use node-oracledb?

A simple query example is:

var oracledb = require('oracledb');

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

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

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

The examples directory and API documentation contain more examples.

What is the future for node-oracledb?

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

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

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

node-oracledb Links

Monday Jan 19, 2015

The Easiest Way to Enable Oracle Database Application Development on Mac OS X

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

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

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

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

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

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

The Client

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

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

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

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

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

SQL> 

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

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

Prebuilt VMs

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

The Database

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

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

The OS

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

VirtualBox

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

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

How I Enable Autostarting of Oracle Database for Demonstrations and Development

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

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

Create a new service script

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

Set permissions on the script

# chmod 750 /etc/init.d/dbora

Tell Linux to autostart/stop the service

# chkconfig --add dbora
# chkconfig dbora on

Edit /etc/oratab

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

Starting / Stopping the DB

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

Or it can be manually controlled with:

# service dbora start

and

# service dbora stop

Starting Oracle Database 12c Multitenant PDBs

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

connect / as sysdba

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

Monday Jan 12, 2015

We are looking to hire someone who loves working with open source developer communities.

We are looking to hire someone who loves working with open source developer communities.

The ideal candidate would have experience with the Oracle Database and at least two or more open source developer environments. The successful candidate would join a team of people responsible for helping database app developers be more productive while using prominent open source development tools. That person would also help to represent the needs of the community back to Oracle development. If you are a technical person that would like to make a difference, please check out this link.

Monday Dec 08, 2014

Video: Best Practices for Application Performance, Scalability, and Availability

Nancy Ikeda nails it in a great Oracle OpenWorld recording of her Best Practices for Application Performance, Scalability, and Availability session now viewable on the Oracle Call Interface page

The session covered:

Best practice coding samples and techniques show how to resolve connection management, statement execution, and data fetching inefficiencies in applications using APIs such as JDBC, OCI, ODBC, ODP.Net, or higher-level scripting languages. This session shows how the Automatic Workload Repository feature of Oracle Database and Automatic Database Diagnostic Monitor profiling tools help diagnose application design and coding issues. Specific solutions show how to resolve these and other issues to enhance applications for scalability and resilience. Among the solutions discussed are Oracle Database 12c’s new client configuration file. Developers or DBAs can use it to tune and configure applications without modifying code. Examples use JDBC and OCI but are applicable to all APIs.

Nancy is one of Oracle's senior developers working in the call interface group.

About

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

Follow:
Blaine Carter
Dan McGhan

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today