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
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
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)
Start the Virtual Machine, and login with:
- username: oracle
- password: oracle
Once logged in, you should have an IP address which will appear when the terminal opesn. If that doesn't work, try executing:
Look for the en0 section. You should be able to ssh in to that address, like so:
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
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
Installing Instant Client on the Mac
Download and unzip the following files (NOTE: you want the 32-bit version - even if you have a 64-bit operating system):
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:
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.