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.
mike.williamson 