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
require the gem again, it will still fail because it still Cannot find OCI.DLL in 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?
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:
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>
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.
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.