CHEF - Using an Oracle Database

There are a lot of cookbooks that let you run queries/operations against open source databases (MySQL/PostgreSQL etc). I wanted to use something to deal with Oracle database. I found an old blog on Oracle's website that shows few examples of this integration using oci8. But oci8 works specifically for Oracle and while it solved my problem, I couldn't use it for other databases. A friend recommended me Sequel which I found really useful. But using Oracle with Sequel still requires you to install and configure oci8. Here's the process:

Example below is for configuring on Mac OS. See footnotes for Linux.

Step 1
Install Ruby gem oci8. To install oci8, you must have at a minimum Oracle Instant client and SDK. Download them from Oracle's website.

Step 2
Copy the zip files to the homebrew library folder. If you are not using homebrew, you should!
ls /Library/Caches/Homebrew
instantclient-sdk-macos.x64-11.2.0.4.0.zip
instantclient-basic-macos.x64-11.2.0.4.0.zip

Step 3
Install the packages using homebrew.

brew install InstantClientTap/instantclient/instantclient-basic
==> Tapping instantclienttap/instantclient
Cloning into '/usr/local/Library/Taps/instantclienttap/homebrew-instantclient'...
Tapped 4 formulae (43 files, 37.9K)
==> Installing instantclient-basic from instantclienttap/instantclient
==> Downloading http://download.oracle.com/otn/mac/instantclient/11204/instantclient-basic-macos.x64-11.2.0.4.0.zip
Already downloaded: /Library/Caches/Homebrew/instantclient-basic-macos.x64-11.2.0.4.0.zip
==> /usr/bin/install_name_tool -id /usr/local/lib/libclntsh.dylib.11.1 /usr/local/Cellar/instantclient-basic/11.2.0.4.0/lib/libclntsh.dylib
.... [output clipped]
🍺  /usr/local/Cellar/instantclient-basic/11.2.0.4.0: 8 files, 181.0M, built in 3 seconds
/Library/Caches/Homebrew >

brew install InstantClientTap/instantclient/instantclient-sdk
==> Installing instantclient-sdk from instantclienttap/instantclient
==> Downloading http://download.oracle.com/otn/mac/instantclient/11204/instantclient-sdk-macos.x64-11.2.0.4.0.zip
Already downloaded: /Library/Caches/Homebrew/instantclient-sdk-macos.x64-11.2.0.4.0.zip
🍺  /usr/local/Cellar/instantclient-sdk/11.2.0.4.0: 40 files, 1.9M, built in 0 seconds
/Library/Caches/Homebrew >

Step 4
Install the oci8 Ruby gem, in this case I want to use it with chef so I am using chef's syntax to install the gem, it will work for just ruby too (without preceding the command with chef)

chef gem install ruby-oci8
Building native extensions.  This could take a while...
Successfully installed ruby-oci8-2.2.2
1 gem installed

Step 5
Install the Sequel Ruby gem.

chef gem install sequel
Fetching: sequel-4.34.0.gem (100%)
Successfully installed sequel-4.34.0
1 gem installed

That's it! You should now be able to use Sequel gem to query Oracle databases.

Important note for Linux:
If you want the CHEF recipes to work on your target systems (say Linux), these packages must be installed on them. The problem is that if you attempt to install these gems using chef_gem package in a recipe, the oci8 gem will fail to execute unless you set the LD_LIBRARY_PATH which at the time of writing I couldn't figure out how to. So as a workaround, I installed the Oracle instant client packages (rpm) manually, set the LD_LIBRARY_PATH to where libclntsh.so file was. In my case it was export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib and then installed the gems using the CHEF recipe. Make sure you are in the same session or the LD_LIBRARY_PATH is set at the profile level.