Oracle from Ruby on Windows - OCI, Sequel and NLS_LANG

Accessing the Oracle database from Ruby on Windows requires a few different things but generally works fine. This post covers some of the things that you would encounter and also shows how to access Oracle using Sequel so that you can get started. The information is collected from different places on the Internet but I’ve compiled it all here for ease of access.

In short, using Oracle works perfectly fine on Windows at least with Ruby 2.6. Make sure that you have a full Ruby installation along with the DevKit (follow instructions on this page, for example, but grab Ruby 2.6 or Ruby 2.7 instead). I just have not had the chance to try this with Ruby 3.0 yet.

Here are the steps that we will walk through:

  • Ruby OCI8 gem
  • Oracle Dependencies on Windows
  • Setting PATH
  • Setting NLS_LANG
  • Installing Sequel
  • Connecting from Sequel

Install Ruby OCI8

We need to use the Ruby OCI8 gem for accessing the database. The gen has detailed installation instructions that you won’t read if you just install the gem and try to figure things out. So, please: read the document there!

$ gem install ruby-oci8
Fetching ruby-oci8-2.2.9-x64-mingw32.gem
Successfully installed ruby-oci8-2.2.9-x64-mingw32
Parsing documentation for ruby-oci8-2.2.9-x64-mingw32
Installing ri documentation for ruby-oci8-2.2.9-x64-mingw32
Done installing documentation for ruby-oci8 after 4 seconds
1 gem installed

Once the gem is installed, the natural instinct is to start up irb and see if it works.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
irb(main):006:0> require 'ruby-oci8'
Traceback (most recent call last):
       14: from C:/Ruby26-x64/bin/irb.cmd:31:in `<main>'
       13: from C:/Ruby26-x64/bin/irb.cmd:31:in `load'
       12: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/irb-1.0.0/exe/irb:11:in `<top (required)>'
       11: from (irb):6
       10: from C:/Ruby26-x64/lib/ruby/site_ruby/2.6.0/rubygems/core_ext/kernel_require.rb:149:in `require'
        9: from C:/Ruby26-x64/lib/ruby/site_ruby/2.6.0/rubygems/core_ext/kernel_require.rb:160:in `rescue in require'
        8: from C:/Ruby26-x64/lib/ruby/site_ruby/2.6.0/rubygems/core_ext/kernel_require.rb:160:in `require'
        7: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/ruby-oci8-2.2.9-x64-mingw32/lib/ruby-oci8.rb:1:in `<top (required)>'
        6: from C:/Ruby26-x64/lib/ruby/site_ruby/2.6.0/rubygems/core_ext/kernel_require.rb:85:in `require'
        5: from C:/Ruby26-x64/lib/ruby/site_ruby/2.6.0/rubygems/core_ext/kernel_require.rb:85:in `require'
        4: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/ruby-oci8-2.2.9-x64-mingw32/lib/oci8.rb:113:in `<top (required)>'
        3: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/ruby-oci8-2.2.9-x64-mingw32/lib/oci8.rb:115:in `rescue in <top (required)>'
        2: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/ruby-oci8-2.2.9-x64-mingw32/lib/oci8/check_load_error.rb:138:in `check_load_error'
        1: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/ruby-oci8-2.2.9-x64-mingw32/lib/oci8/check_load_error.rb:42:in `check_os_specific_load_error'
LoadError (Cannot find OCI.DLL in PATH.)

The error tells us directly what the problem is: LoadError (Cannot find OCI.DLL in PATH.)

Oracle Dependencies on Windows

So, we know that we could not load the Ruby-OCI8 gem because it needs OCI.DLL – this needs the Oracle client package to be available on the system. The details are in the documentation for the Ruby OCI8 Installation and specifically, you need to see the details on this page for installing the Oracle Instant Client on Windows.

The files you need are at the Oracle site – the page is for Version 19.12. From that page, I downloaded the following packages – note that I am using a 64-bit Ruby, so I downloaded the 64-bit packages. If you are using a 32-bit Ruby (even on a 64-bit Windows), you need to get the 32-bit packages for Oracle.

  • Instant Client Package – Basic (I got Basic but you could also get Basic Lite)
  • Instant Client Package – SDK
  • Instant Client Package – SQL*Plus

You need to decide where to unzip these packages. I have a path d:\stack under which I add packages like these. I unzipped all these 3 files into d:\stack\orcale so the tree looks like this.

D:\STACK\ORACLE
└───instantclient_19_12
    ├───sdk
    │   ├───admin
    │   ├───demo
    │   ├───include
    │   └───lib
    │       ├───bc
    │       └───msvc
    │           └───vc14
    └───vc14

If you try to go to irb and require the gem again, it will still fail because it still Cannot find OCI.DLL in PATH.

Setting PATH

If you set up exactly like I did, you would find that OCI.DLL is at: D:\stack\oracle\instantclient_19_12 (basically in the instantclient_19_12 folder that you created). This folder needs to be added to the PATH.

For simplicity, while trying things out, you can do this in your command prompt.

$ set PATH=D:\stack\oracle\instantclient_19_12;%PATH%
$
$ irb
irb(main):001:0> require 'ruby-oci8'
Warning: NLS_LANG is not set. fallback to US7ASCII.
=> true

Great! It seems to work! There is a chance that things might still fail. If that happens, you might need to install a Visual C++ runtime library as explained on the Ruby OCI8 installation page. I did not need to, but if you do, could you leave a comment below or get in touch on Twitter/onghu or Email so that I can update this page on exactly what was needed.

So, what’s with that warning?

Setting NLS_LANG

For the client to correctly interpret the data, you need to set the language. You can read a bit more about it on this page but for simplicty, in most cases, you can do something like this:

set NLS_LANG=American_America.UTF8

The page above has details also about how to set the variable in the environment. Now, it all looks good.

$ set NLS_LANG=American_America.UTF8
$ 
$ irb
irb(main):001:0> require 'ruby-oci8'
=> true
irb(main):002:0>

Installing Sequel

We can use Sequel to access the database. Let’s install sequel now.

$ gem install sequel
Fetching sequel-5.47.0.gem
Successfully installed sequel-5.47.0
Parsing documentation for sequel-5.47.0
Installing ri documentation for sequel-5.47.0
Done installing documentation for sequel after 88 seconds
1 gem installed

Connecting to Oracle from within Sequel

If you want to connect from Sequel, you can walk through it in irb by doing something similar.

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
require 'ruby-oci8'
require 'sequel'

# Set the parameters
db_host = '...your oracle host..'
db_port = 1521
db_pass = 'myUserName'
db_user = 'myP@SSw0rD'
db_svc = 'dataBaseName'

# Connect to the DB Server
DB = Sequel.connect(adapter: :oracle, user: db_user, password: db_pass, host: db_host, port: db_port, database: db_svc)

# Assuming your praemeters are fine, you will be able to connect

# You can try to find out what tables are in the database, you can now do something like
DB['SELECT * FROM user_tables'].each do |row|
  p row
end

# Let's say you have a table called system_configs. You could do
configs = DB.from(:system_configs)
configs.count

# or something like this
configs = DB[:system_configs]
configs.where(name: 'main_config').get(:value)

# ...and so on!
# check the sequel page for more examples

How I arrange it

Since I don’t use Oracle + Ruby on Windows most of the time (I’m only doing it to connect to a server on Amazon RDS Oracle to test something out), I don’t want to augment the PATH or add the environment variable. For this reason, I create a command file (setenv.cmd) and add these all to the file. Then, I run the file in the command prompt before running my Ruby scripts.

1
2
3
4
5
6
7
set DB_HOST_NAME=host.ap-southeast-1.rds.amazonaws.com
set DB_PORT_NO=1521
set DB_SERVICE_NAME=orcl
set DB_USER_NAME=USERName
set DB_PASSWORD=p@ssW0rd
set NLS_LANG=American_America.UTF8
set PATH=D:\stack\oracle\instantclient_19_12;%PATH%

That way, the environment variables are set. Ruby-OCI8 finds NLS_LANG and the OCI.DLL is now on the PATH. In addition, I use the method described in this post to access the environment variables from my Ruby script rather than hard-coding the values into the script.

References/ Links

These are some of the sources that I looked at while compiling the post:

  • More on Ruby OCI8 is at https://github.com/kubo/ruby-oci8
  • More on sequel is at: https://github.com/jeremyevans/sequel
  • Wiki page for Ruby OCI8 Installation is at https://github.com/kubo/ruby-oci8/blob/master/docs/install-instant-client.md
  • Details for installing the Oracle Instant Client are at https://github.com/kubo/ruby-oci8/blob/master/docs/install-instant-client.md

I hope that this helps you a bit in setting up with Oracle and Ruby on Windows. Most of this is just for me to remember what I did this time. If it helps someone else, then that’s great. If you do have other things you’d like to add, please leave a comment.

comments powered by Disqus