Setting up Oracle-XE on Arch linux

Currently I’m working on a project that needs to pull data from an Oracle database. My normal development setup is to install the database locally and develop the application TDD style with a test database, so it seemed reasonable to do the same with Oracle as well. Although, the fact that this became fodder for a blog post suggests it wasn’t as easy as I expected.

First up was the basic decision about what to install. The Oracle database itself is a multi-gigabyte monster, seemingly designed to sell support contracts so I was glad to find discover an Express Edition exists. Last released in 2014, and aimed at whatever “easy development” means in the world of Oracle, apparently “Applications developed with XE may be immediately used with other editions of the Oracle Database”. This sounds like the right thing to me.

So “easy development” obviously begins with logging into your Oracle account and downloading the Oracle XE zip file.

Next we want to package this up so that it can be cleanly installed and removed from our system. The Oracle-XE package on the AUR uses the zip file we just downloaded to build a package we can install, so lets get that happening

[mike@longshot ~]$ git clone https://aur.archlinux.org/oracle-xe.git
Cloning into 'oracle-xe'...
remote: Counting objects: 22, done.
remote: Compressing objects: 100% (13/13), done.
remote: Total 22 (delta 8), reused 22 (delta 8)
Unpacking objects: 100% (22/22), done.
[mike@longshot ~]$ cd oracle-xe                           
[mike@longshot oracle-xe]$ cp ~/Downloads/oracle-xe-11.2.0-1.0.x86_64.rpm.zip .
[mike@longshot oracle-xe]$ ls
listener.ora  oracle_env.csh  oracle_env.sh  oracle.install  oracle-xe  oracle-xe-11.2.0-1.0.x86_64.rpm.zip  oracle-xe.conf  oracle-xe.service  PKGBUILD
[mike@longshot oracle-xe]$ makepkg
==> Making package: oracle-xe 11.2.0_1.0-4 (Tue Oct 17 14:51:00 EDT 2017)
==> Checking runtime dependencies...
==> Checking buildtime dependencies...
==> Retrieving sources...
  -> Found oracle-xe-11.2.0-1.0.x86_64.rpm.zip
  -> Found oracle_env.csh
  -> Found oracle_env.sh
  -> Found oracle-xe
  -> Found oracle-xe.conf
  -> Found listener.ora
  -> Found oracle-xe.service
==> Validating source files with md5sums...
    oracle-xe-11.2.0-1.0.x86_64.rpm.zip ... Passed
    oracle_env.csh ... Passed
    oracle_env.sh ... Passed
    oracle-xe ... Passed
    oracle-xe.conf ... Passed
    listener.ora ... Passed
    oracle-xe.service ... Passed
==> Extracting sources...
  -> Extracting oracle-xe-11.2.0-1.0.x86_64.rpm.zip with bsdtar
==> Starting build()...
==> Entering fakeroot environment...
==> Starting package()...
==> Tidying install...
  -> Removing libtool files...
  -> Purging unwanted files...
  -> Removing static library files...
  -> Compressing man and info pages...
==> Checking for packaging issue...
==> Creating package "oracle-xe"...
  -> Generating .PKGINFO file...
  -> Generating .BUILDINFO file...
  -> Adding install file...
  -> Generating .MTREE file...
  -> Compressing package...
==> Leaving fakeroot environment.
==> Finished making: oracle-xe 11.2.0_1.0-4 (Tue Oct 17 14:54:35 EDT 2017)
[mike@longshot oracle-xe]$ ls
listener.ora  oracle_env.csh  oracle_env.sh  oracle.install  oracle-xe  oracle-xe-11.2.0_1.0-4-x86_64.pkg.tar.xz  oracle-xe-11.2.0-1.0.x86_64.rpm.zip  oracle-xe.conf  oracle-xe.service  pkg  PKGBUILD  src
[mike@longshot oracle-xe]$ sudo pacman -U oracle-xe-11.2.0_1.0-4-x86_64.pkg.tar.xz 
[sudo] password for mike: 
loading packages...
resolving dependencies...
looking for conflicting packages...

Packages (1) oracle-xe-11.2.0_1.0-4

Total Installed Size:  564.61 MiB

:: Proceed with installation? [Y/n] y
(1/1) checking keys in keyring                                                                                                 [#############################################################################] 100%
(1/1) checking package integrity                                                                                               [#############################################################################] 100%
(1/1) loading package files                                                                                                    [#############################################################################] 100%
(1/1) checking for file conflicts                                                                                              [#############################################################################] 100%
(1/1) checking available disk space                                                                                            [#############################################################################] 100%
:: Processing package changes...
(1/1) installing oracle-xe                                                                                                     [#############################################################################] 100%

creating group "dba" ...done

creating user "oracle" ...done

change directory rights ...done

set sticky bit to oracle executable ...done

creating /etc/sysconfig ...done

creating /var/log/oracle ...done


add your user to the "dba" group in order to use the oracle tools

:: Running post-transaction hooks...
(1/2) Arming ConditionNeedsUpdate...
(2/2) Updating the desktop file MIME type cache...
[mike@longshot oracle-xe]$ sudo usermod -aG dba $USER

Above we built and installed the Oracle-XE package, and added the dba group to the current users existing groups.

To get a sense of what we just installed it’s good to look at what that package put into the /etc directory.

[mike@longshot node_oracle]$ pacman -Ql oracle-xe | grep "etc"
oracle-xe /etc/
oracle-xe /etc/ld.so.conf.d/
oracle-xe /etc/ld.so.conf.d/oracle-xe.conf
oracle-xe /etc/profile.d/
oracle-xe /etc/profile.d/oracle_env.csh
oracle-xe /etc/profile.d/oracle_env.sh
oracle-xe /etc/rc.d/
oracle-xe /etc/rc.d/oracle-xe
oracle-xe /etc/systemd/
oracle-xe /etc/systemd/system/
oracle-xe /etc/systemd/system/oracle-xe.service
[mike@longshot node_oracle]$ cat /etc/ld.so.conf.d/oracle-xe.conf
/usr/lib/oracle/product/11.2.0/xe/lib
[mike@longshot node_oracle]$ cat /etc/profile.d/oracle_env.sh 
export ORACLE_HOME=/usr/lib/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$PATH:$ORACLE_HOME/bin

Here we can see that this package installed an entry in our library search path (/etc/ld.so.conf.d/oracle-xe.conf), added some env vars for us (/etc/profile.d/oracle_env.sh), added a run script (/etc/rc.d/oracle-xe) and a systemd service (/etc/systemd/system/oracle-xe.service).

In theory we should be able to install our node driver and have it work.

Installing node-oracledb

Oracle has thoughtfully released a Node.js driver, which can be installed with npm install oracledb. This driver installs and compiles a bunch of stuff with node-gyp and expects some libraries and headers to be available for that process. Let’s see!

[mike@longshot node_oracle]$ npm i oracledb

> oracledb@1.13.1 install /home/mike/projects/play/node_oracle/node_modules/oracledb
> node-gyp rebuild

node-oracledb ERR! Error: Cannot find $OCI_LIB_DIR/libclntsh.so
node-oracledb ERR! Error: See https://github.com/oracle/node-oracledb/blob/master/INSTALL.md

gyp: Call to 'INSTURL="https://github.com/oracle/node-oracledb/blob/master/INSTALL.md"; ERR="node-oracledb ERR! Error:"; if [ -z $OCI_LIB_DIR ]; then OCI_LIB_DIR=`ls -d /usr/lib/oracle/*/client*/lib/libclntsh.* 2> /dev/null | tail -1 | sed -e 's#/libclntsh[^/]*##'`; if [ -z $OCI_LIB_DIR ]; then if [ -z "$ORACLE_HOME" ]; then if [ -f /opt/oracle/instantclient/libclntsh.so ]; then echo "/opt/oracle/instantclient/"; else echo "$ERR Cannot find Oracle library libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; else if [ -f "$ORACLE_HOME/lib/libclntsh.so" ]; then echo $ORACLE_HOME/lib; else echo "$ERR Cannot find \$ORACLE_HOME/lib/libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; fi; else if [ -f "$OCI_LIB_DIR/libclntsh.so" ]; then echo $OCI_LIB_DIR; else echo "$ERR Cannot find \$OCI_LIB_DIR/libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; fi; else if [ -f "$OCI_LIB_DIR/libclntsh.so" ]; then echo $OCI_LIB_DIR; else echo "$ERR Cannot find \$OCI_LIB_DIR/libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; fi;' returned exit status 0 while in binding.gyp. while trying to load binding.gyp
gyp ERR! configure error 
gyp ERR! stack Error: `gyp` failed with exit code: 1
gyp ERR! stack     at ChildProcess.onCpExit (/home/mike/.nodenv/versions/8.7.0/lib/node_modules/npm/node_modules/node-gyp/lib/configure.js:336:16)
gyp ERR! stack     at emitTwo (events.js:125:13)
gyp ERR! stack     at ChildProcess.emit (events.js:213:7)
gyp ERR! stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:200:12)
gyp ERR! System Linux 4.13.8-1-hardened
gyp ERR! command "/home/mike/.nodenv/versions/8.7.0/bin/node" "/home/mike/.nodenv/versions/8.7.0/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "rebuild"
gyp ERR! cwd /home/mike/projects/play/node_oracle/node_modules/oracledb
gyp ERR! node -v v8.7.0
gyp ERR! node-gyp -v v3.6.2
gyp ERR! not ok 
npm WARN node_oracle@1.0.0 No description
npm WARN node_oracle@1.0.0 No repository field.

npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! oracledb@1.13.1 install: `node-gyp rebuild`
npm ERR! Exit status 1
npm ERR! 
npm ERR! Failed at the oracledb@1.13.1 install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     /home/mike/.npm/_logs/2017-10-19T21_01_24_226Z-debug.log

Not being a C/C++ programmer, these moments are pretty perplexing. It looks a lot like ORACLE_HOME=/usr/lib/oracle/product/11.2.0/xe OCI_LIB_DIR=$ORACLE_HOME/lib OCI_INC_DIR=$ORACLE_HOME/xdk/include npm i oracledb should work, but it doesn’t.

ldconfig -N -v | grep libclntsh.so prints out libclntsh.so.11.1 -> libclntsh.so.11.1 so the library seems to be findable, just not by the driver.

Plan B

It turns out that the headers and libraries we need are also available in Oracle’s instantclient. This would mean more downloading/packaging silliness except someone has gone to the effort to package these instantclient libraries and providing the as a pacman repo. Since the world is a beautiful place and everyone is friends on the internet I am going to pull my packages from them by adding these lines to my pacman.conf:

[mike@longshot node_oracle]$ tail -n 3 /etc/pacman.conf 
[oracle]
SigLevel = Optional TrustAll
Server = http://linux.shikadi.net/arch/$repo/$arch/

Then we update and install.

[mike@longshot node_oracle] sudo pacman -Sy
[mike@longshot node_oracle]$ sudo pacman -S oracle-instantclient-sdk oracle-instantclient-basic

Looking at the contents pacman -Ql oracle-instantclient-sdk shows bunch of files being put into /usr/include, while pacman -Ql oracle-instantclient-basic shows our much sought after libclntsh.so going into /usr/lib. It looks like we finally have some plausible values for OCI_LIB_DIR and OCI_INC_DIR.

[mike@longshot node_oracle]$ OCI_LIB_DIR=/usr/lib OCI_INC_DIR=/usr/include npm i oracledb

> oracledb@1.13.1 install /home/mike/projects/play/node_oracle/node_modules/oracledb
> node-gyp rebuild

make: Entering directory '/home/mike/projects/play/node_oracle/node_modules/oracledb/build'
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsOracle.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsPool.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsConnection.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsResultSet.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsMessages.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsIntLob.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnv.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnvImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiException.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiExceptionImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiConnImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiDateTimeArrayImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiPoolImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiStmtImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiUtils.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiLob.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiCommon.o
  SOLINK_MODULE(target) Release/obj.target/oracledb.node
  COPY Release/oracledb.node
make: Leaving directory '/home/mike/projects/play/node_oracle/node_modules/oracledb/build'
npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN node_oracle@1.0.0 No description
npm WARN node_oracle@1.0.0 No repository field.

+ oracledb@1.13.1
added 2 packages in 11.894s

Talking to Oracle-XE from Node

After installing XE, instandclient-basic and sdk, the full set of environmental variable that made this thing work are:

export ORACLE_HOME=/usr/lib/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$PATH:$ORACLE_HOME/bin
export OCI_INC_DIR=/usr/include
export OCI_LIB_DIR=/usr/lib

Next up I want to configure XE (which seems to need those vars set). Below I’ll use sudo -E to ensure that all of those variable still exist when I do sudo:

[mike@longshot node_oracle]$ sudo -E /etc/rc.d/oracle-xe configure
[sudo] password for mike: 

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

In theory XE is configured and running (in the future you’ll probably want to start it with systemctl start oracle-xe), and the node-oracle README suggests that we run one of the examples to test it. What they don’t mention is that the example is based on sample data in an “hr” account that needs to be enabled first.

[mike@longshot oracle-xe]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 20 14:20:30 2017

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

SQL> connect system/yourpassword as sysdba
Connected.
SQL> ALTER USER hr ACCOUNT UNLOCK;          
User altered.
SQL> ALTER USER hr IDENTIFIED BY password;         
User altered.

The example script reads it’s config from a file so I created that using the terrible password I assigned to the hr account above:

[mike@longshot node_oracle]$ cat dbconfig.js 
module.exports = {
    user: "hr",
    password: "password",
    connectString: "localhost/XE",
};

So now we should be able to run the example:

[mike@longshot node_oracle]$ node select1.js 
[ { name: 'DEPARTMENT_ID' }, { name: 'DEPARTMENT_NAME' } ]
[ [ 180, 'Construction' ] ]

What’s next

The next logical step here is to start exploring the capabilities of the Node driver. There is also the Simple-oracledb package which is suddenly sounding very interesting to me.
Hopefully this will save someone else some time.

Advertisements

Working with the Google Vision API

I remember hearing a story about a developer whose contract with the military specified the number of kilos of documentation that were required to accompany the system they were building. I think of that story from time to time when I use Google products.

Google’s Vision API gives access to legit state-of-the-art Artificial Intelligence and is amazing for extracting text from images, but a concise modern example doesn’t seem to exist in spite of the huge volume of documentation.

The example they give is in the classic callback style:

var vision = require('@google-cloud/vision');

var visionClient = vision({
  projectId: 'grape-spaceship-123',
  keyFilename: '/path/to/keyfile.json'
});

visionClient.detectText('./image.jpg', function(err, text) {
  // text = [
  //   'This was text found in the image',
  //   'This was more text found in the image'
  // ]
});

With all that has been written about the inversion of control problems of callbacks and ES2015 support nearly complete and in wide use thanks to Babel, examples like this are feeling distinctly retro.

Also painful for anyone working with Docker is that the authentication appears to require me to include a keyfile.json somewhere in my container, where what I actually want is to store that stuff in the environment.

After a bit of experimentation, it turns out that that the google-cloud-node library doesn’t let us down. It’s filled with all the promisey goodness we scripters-of-java have come to expect. If you are using jest this test should get you going:

import Vision from '@google-cloud/vision'

describe('Google Vision client', () => {

  it('successfully connects', async () => {
    let client = Vision({
      projectId: process.env.GOOGLE_VISION_PROJECT_ID,
      credentials: {
	      private_key: process.env.GOOGLE_VISION_PRIVATE_KEY.replace(/\\n/g, '\n'),
        client_email: process.env.GOOGLE_VISION_CLIENT_EMAIL
      }
    })

    let [[text, ...words], annotations] = await client.detectText(__dirname + '/data/foo.jpg')
    expect(text).toEqual("foo bar\n")
    expect(words).toContain("foo", "bar")
  })

})

The project id is easy enough to find, but the environment variables used to avoid the keyfile.json are actually found within the keyfile.

{
  "type": "service_account",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "...@developer.gserviceaccount.com",
  "client_id": "...",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/..."
}

The keyfile above was created by going to the credentials console and following the instructions here.

Note the replace(/\\n/g, '\n') happening on the GOOGLE_VISION_PRIVATE_KEY. This is from issue 1173 and without it you end up with the error
Error: error:0906D06C:PEM routines:PEM_read_bio:no start line. Replacing new lines with new lines seems silly but you gotta do what you gotta do.

The last missing piece is an image with some text. I created a quick test image in Gimp with the words “foo bar”:

foo

While it wasn’t clear at first glance, google-cloud-node is a pretty sophisticated and capable library, despite being theoretically “alpha”. Google is remaking itself as “the AI company” and the boundary pushing stuff it’s doing means I’m probably going to be using this client a lot. I really was hoping to find a small amount of the “right” documentation instead of the huge volume of partial answers spread across their sprawling empire. Hopefully this is a useful contribution towards that reality.