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#619e9a8fa6625a2c5ca3e1a2ba10dbdaab5ae900",
    	"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.

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.

Monday Nov 17, 2014

Configuring Python cx_Oracle and mod_wsgi on Oracle Linux

The Web Server Gateway Interface (WSGI) is a standardized interface between web servers and Python web frameworks or applications. Many frameworks including Django support WSGI.

This post is a brief how-to about configuring Apache's mod_wsgi with Python's cx_Oracle driver for Oracle Database. The steps are for Oracle Linux.

  1. Download Instant Client Basic & SDK ZIP files from OTN. For cx_Oracle 5.1, use the ZIPs, not the RPMs.

  2. As root, unzip the files to the same directory, e.g. /opt/oracle/instantclient_12_1:

    mkdir /opt/oracle
    cd /opt/oracle
    unzip /tmp/instantclient-basic-linux.x64-12.1.0.2.0.zip
    unzip /tmp/instantclient-sdk-linux.x64-12.1.0.2.0.zip
    
  3. Configure Instant Client:

    cd /opt/oracle/instantclient_12_1
    ln -s libclntsh.so.12.1 libclntsh.so
    
  4. Install the pip package management tool for Python by following pip.readthedocs.org/en/latest/installing.html and downloading get-pip.py. Then run:

    python get-pip.py
    
  5. Install cx_Oracle:

    export LD_RUN_PATH=/opt/oracle/instantclient_12_1
    export ORACLE_HOME=/opt/oracle/instantclient_12_1
    pip install cx_Oracle
    

    The key here is the use of LD_RUN_PATH. This obviates the need to later set LD_LIBRARY_PATH or configure ldconfig for cx_Oracle to find the Instant Client libraries. There is a cx_Oracle-specific variable FORCE_RPATH which has the same effect.

    Note the cx_Oracle installer overloads the meaning of ORACLE_HOME. This variable is not normally used with Instant Client.

    Neither ORACLE_HOME or LD_RUN_PATH need to be set at runtime.

    If you don't use LD_RUN_PATH or FORCE_RPATH during installation, you will need to make LD_LIBRARY_PATH available to the Apache process or use ldconfig to add Instant Client to the system wide library search path.

    Configuring ldconfig is an effective and commonly used solution. However it has a potential problem that if multiple Oracle products exist, with possibly differing versions of Oracle libraries on the same machine, then there might be library clashes. If you wish to use it, create a file /etc/ld.conf.so.d/oracle-instant-client.conf containing:

    /opt/oracle/instantclient_12_1
    

    Then update the linker cache by running:

    ldconfig

    Alternatively set LD_LIBRARY_PATH in Apache's environment file, /etc/sysconfig/httpd. In Oracle Linux 6 use:

    export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1
    

    In Oracle Linux 7 use:

    LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1
    

    In Oracle Linux 7, don't reference variables on the right-hand side of the equals sign since they won't be expanded.

    [The Apache environment configuration file location varies between Linux distributions. On OpenSUSE see /etc/sysconfig/apache2. On Debian-based distributions look at /etc/apache2/envvars].

  6. Set any other Oracle environment variables in the Apache environment configuration file /etc/sysconfig/httpd. For example:

    NLS_LANG=GERMAN_GERMANY.AL32UTF8
    

    (Prefix any variable setting with export in Oracle Linux 6)

  7. Install mod_wsgi:

    yum install mod_wsgi
    
  8. Add this line to /etc/httpd/conf/httpd.conf:

    WSGIScriptAlias /wsgi_test /var/www/html/wsgi_test.py
    
  9. On Oracle Linux 6, start the web server with:

    service httpd start
    

    On Oracle Linux 7 use:

    systemctl start httpd.service
    
  10. Create a test file /var/www/html/wsgi_test.py that connects to your database:

    #-*- coding: utf-8 -*-
    
    def query():
        import cx_Oracle
        db = cx_Oracle.connect("hr", "welcome", "localhost/orcl")
        cursor = db.cursor()
        cursor.execute("select city from locations where location_id = 2200")
        return cursor.fetchone()[0]
    
    def wsgi_test(environ, start_response):
        output = query()
    
        status = '200 OK'
        headers = [('Content-type', 'text/plain'),
    	       ('Content-Length', str(len(output)))]
        start_response(status, headers)
        yield output
    
    application = wsgi_test
    
  11. Load http://localhost/wsgi_test in a browser. The city of the queried location id will be displayed.

That's it. Let me know how it works for you.

Information on cx_Oracle can be found here.

Information on Oracle Linux can be found here.

Information on Oracle Database can be found here.

Wednesday Oct 29, 2014

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

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

The official blurb was:

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

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

How to Get the Most out of a Technology Conference (Podcast)

We did good in this recent podcast How to Get the Most out of a Technology Conference (which is cleverly disguised as a video). It has everything the inexperienced conference-goer needs to know. I'm pleased to have been able to give a shout out to PHPWomen! Despite the official blurb, the content applies to all technology conferences and there is very little that is specific to Oracle.

Friday Sep 26, 2014

Oracle OpenWorld is Upon Us! Application Developers Unite!

It's a super, extra, enormously busy time of the year at Oracle because our big Oracle OpenWorld Conference is next week. We have all been working on new products and new presentations.

I have a full mix of events, meetings, presentations and booth duty. If you're at the conference drop by to say Hi. I'll be at the Application Development booth (Left - SLD-163) in Moscone South on Monday morning, Tuesday lunchtime and Wednesday morning (plus other odd times).

Also lookout for me onstage with Tom Kyte on Monday and with Steven Feuerstein on Wednesday.

The session details, and other sessions you might find interesting are listed on my Focus On Application Development page.

If you are a developer, I recommend coming to the Programming and Scripting "Meet the Expert" session at 6pm Tuesday evening (Moscone South - 307). I cannot stress enough how valuable it is to have a dialog and relationship with the developers who create the software and APIs you use.

Tuesday Aug 19, 2014

Routing PHP memcached calls to Oracle Coherence

A new post Getting Started with the Coherence Memcached Adaptor from David Felcey shows how PHP memcached calls can automatically be routed to store data in Oracle Coherence 12c. This is possible now Coherence 12.1.3 supports Memcached clients using the Binary Memcached protocol. David's post shows how the Coherence Memcached adaptor can be configured as a proxy service that runs in the Coherence cluster. There's nothing particular to configure in the PHP application, except to enable memcached.use_sasl = 1

So what is Coherence? It is an "in-memory data grid solution", with a number of advanced features. You can read more in the Oracle Coherence 12C Data Sheet.

Thursday Jul 31, 2014

New Oracle Technology Network PHP Forum URL

The Oracle Technology Network (which promotes the development community) is upgrading its software platform and reorganizing some content. The PHP Oracle forum is now at https://community.oracle.com/community/development_tools/php. The top level "PHP Developer Center" is at http://www.oracle.com/technetwork/database/database-technologies/php/whatsnew/index.html. I notice my old bookmarks for the Developer Center redirect to its current location, but this doesn't seem true of some very old URLs for the forum.

Thursday Apr 24, 2014

Installing PHP on Oracle HTTP Server 12c

OTN have posted an updated guide for installing PHP on Oracle HTTP Server. Check it out!

Using PHP-FPM is a great way to integrate with Oracle Fusion Middleware.

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

Search

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