Developing locally with Oracle and Ruby on a Mac

March 13, 2012 oracle

In this post I'll describe how you can get setup to develop an app backed by an Oracle database using Ruby on a Mac. Specifically I'll cover:

  • How to install and configure a pre-built VM with Oracle running
  • How to install the Oracle instant client on OSX Lion
  • How to compile a 32-bit version of Ruby 1.9.2 w/ RVM
  • How to connect your Ruby app to the Oracle database using ruby-oci8

At a high level, you need to:

  • Install VirtualBox
  • Install an Oracle VM, configure it correctly and add a user
  • Install Oracle's instant client on your mac
  • Get a 32-bit version of ruby running on your mac
  • Setup the correct connection info

Install VirtualBox

Go to https://www.virtualbox.org/wiki/Downloads and download the _VirtualBox 4.1.8 for OS X hosts_ file

Install the Oracle Virtual Machine

Go to http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Download the _Oracle Developer Day.ova_ file and follow the instructions for importing it.

Configuring the Virtual Machine

Before starting, do the following:

  • Increase the video memory so you can get better resolution: Setting > Display > Video Memory
  • Add a CD Drive under Settings > Storage > (green plus sign) and set it to "Empty"
  • Enable network connectivity by going to Settings > Network > Adapter 1 and make sure you have the correct settings (see below)

The correct network settings will vary based on your configuration, but start out with:

  • Attached to: Bridged Adapter
  • Name: en0 Ethernet
  • Advanced: Cable Connected (checked)
Oracle-vm-network-settings

Start the Virtual Machine, and login with:

  • username: oracle
  • password: oracle
Oracle-vm-username Oracle-vm-password

Once logged in, you should have an IP address which will appear when the terminal opesn. If that doesn't work, try executing:

/sbin/ifconfig

Look for the en0 section. You should be able to ssh in to that address, like so:

ssh oracle@10.1.10.30

If you are working on a wireless connection, you will have to configure it slightly differently (using en1: airport) which you can read about here .

You may want to install Guest Additions from _Devices > Install Guest Additions_ (from the running virtual machine's menu) - that's why you needed to install the CD drive.

Setting up the Oracle Database

Start the program called "SQL Developer 3.0" and login with the following credentials:

  • username: sys
  • password: oracle
  • connection type: basic
  • role: sysdba
  • hostname: localhost.localdomain
  • port: 1521
  • sid: orcl
Oracle-sql-developer-connection-info

Once you've entered, create a new user, with the following attributes:

  • username: someuser
  • password: somepassword
  • roles: Grant All, Admin All, Default All
  • System Privileges: Grant All, Admin All

This is the user you'll use to connect from the Mac.

Using the Oracle Web Console

From the virtual machine, you can enter the virtual web console by issuing this command:

emctl start dbconsole

It takes an embarassingly long time to load, but once it does, you can open https://localhost.localdomain:1158/em

To login here, use these settings:

  • username: sys
  • password: oracle
  • connect as: sysdba
Oracle-web-console

Installing Instant Client on the Mac

Go to http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

Download and unzip the following files (NOTE: you want the 32-bit version - even if you have a 64-bit operating system):

  • instantclient-basiclite-10.2.0.4.0-macosx-x86.zip
  • instantclient-sqlplus-10.2.0.4.0-macosx-x86.zip
  • instantclient-sdk-10.2.0.4.0-macosx-x86.zip

On your mac, download the following libraries, then unzip them, then copy the contents of those directories into a single directory. Then move that directory to /usr/local. The resulting directory tree should look like this:

/usr/local/instantclient-10.2.0.4.0/
├── BASIC_README
├── SQLPLUS_README
├── classes12.jar
├── genezi
├── glogin.sql
├── libclntsh.dylib -> libclntsh.dylib.10.1
├── libclntsh.dylib.10.1
├── libnnz10.dylib
├── libocci.dylib.10.1
├── libociei.dylib
├── libocijdbc10.dylib
├── libocijdbc10.jnilib
├── libsqlplus.dylib
├── libsqlplusic.dylib
├── ojdbc14.jar
├── sdk
│   ├── SDK_README
│   ├── demo
│   │   ├── cdemo81.c
│   │   ├── demo.mk
│   │   ├── occidemo.sql
│   │   ├── occidemod.sql
│   │   ├── occidml.cpp
│   │   ├── occiobj.cpp
│   │   └── occiobj.typ
│   ├── include
│   │   ├── nzerror.h
│   │   ├── nzt.h
│   │   ├── occi.h
│   │   ├── occiAQ.h
│   │   ├── occiCommon.h
│   │   ├── occiControl.h
│   │   ├── occiData.h
│   │   ├── occiObjects.h
│   │   ├── oci.h
│   │   ├── oci1.h
│   │   ├── oci8dp.h
│   │   ├── ociap.h
│   │   ├── ociapr.h
│   │   ├── ocidef.h
│   │   ├── ocidem.h
│   │   ├── ocidfn.h
│   │   ├── ociextp.h
│   │   ├── ocikpr.h
│   │   ├── ocixmldb.h
│   │   ├── odci.h
│   │   ├── oratypes.h
│   │   ├── ori.h
│   │   ├── orid.h
│   │   ├── orl.h
│   │   ├── oro.h
│   │   ├── ort.h
│   │   └── xa.h
│   ├── ott
│   └── ottclasses.zip
└── sqlplus

Once that's in place, you have to run:

cd /usr/local/instantclient-10.2.0.4.0/
ln -s libclntsh.dylib.10.1 libclntsh.dylib

Now you have all the oracle-related things installed, and you have to get your Ruby setup correct. In order for ruby to see these files, you'll have to add them to your bash profile:

In ~/.bash_profile

export DYLD_LIBRARY_PATH=/usr/local/instantclient-10.2.0.4.0
export ORACLE_HOME=/usr/local/instantclient-10.2.0.4.0
export SQLPATH=$ORACLE_HOME
export NLS_LANG="AMERICAN_AMERICA.UTF8"

Install a 32-bit version of Ruby 1.9.2 on your machine

On command line, run the following (this assumes you have RVM - if not, go get it ):

rvm get stable
# close your shell and reopen - to be sure you are on the latest RVM
rvm cleanup all
rvm_archflags="-arch i386" CFLAGS="-arch i386" LDFLAGS="-arch i386" rvm install 1.9.2 --patch osx-arch-fix -n i386
rvm alias create i386 ruby-1.9.2-p290-i386

This will install a 32-bit version of ruby, aliased to i386, and your other versions of 1.9.2 will not been affected.

Modify the .rvmrc file in your project directory to look like this:

rvm i386@my-project-name --create

You can read more about this craziness here .

At this point it's probably worth closing your terminal windows and re-opening to make sure you have a clean RVM setup, then cd to your project directory.

You should now be able to install the ruby-oci8 gem and test it out:

gem install ruby-oci8
irb
> require 'rubygems'
> require 'oci8'
> connection = OCI8.new("someuser", "somepassword", "//10.1.10.30:1521/orcl")
When connecting to the Oracle database, you use the IP address (or hostname), port and instance name for the "database name".

Running commands with ruby-oci8

If you are using Rails, you can set your database connection to use a similar configuration to the one above.

If you are using plain-old-ruby, you can execute Oracle commands directly, like so:

require 'rubygems'
require 'oci8'
connection = OCI8.new("someuser", "somepassword", "//10.1.10.30:1521/orcl")
connection.exec("create table foo (bar varchar(20) not null)")

One gotcha I noticed was that sometimes OCI complains when you execute a statement that has a semi-colon at the end, like so:

connection.exec("create table foo (bar varchar(20) not null);")

And that's it! If you've gotten this far, you deserve some sort of mood-altering drug. Probably some sort of stimulant, since you are probably as depressed as I was having to go through that ridiculous setup.

Tags