S

c

r

am

b

l

e

d Ch

a

n

n

e

L

Oracle and TOra on Ubuntu Hardy

I work with Oracle a lot in my 9 to 5 existence so I like to have a setup that I can make mistakes on at home. It’s also cool to have a powerful open source tool like TOra at your disposal. I’ve found that it’s easy enough to get them both set up on Ubuntu Hardy.

Oracle (perhaps with one eye on MySQL and Postgres) started giving away binaries of their express edition a few years ago. There are restrictions on how large a database can be and it’s not free as in ‘freedom’ but it does allow you to brush up on Oracle specific features.

Installation is pretty straightforward. If you’re running a Debian based distro (like Ubuntu) you can get Oracle up and running by doing the following. First add their repository to your sources.list. Create a file named “/etc/apt/sources.list.d/oracle.list” containing the following line:

deb http://oss.oracle.com/debian unstable main non-free

Add their key:

# wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -

Update and install:

# sudo apt-get update
# sudo apt-get install oracle-xe oracle-xe-client

After the install is complete, you need to run the configuration script:

# sudo /etc/init.d/oracle-xe configure

You will be asked a few questions. The default values are probably suitable though you might want to think about whether you want the db server to start at boot (I prefer to start it manually when required). Once completed, you should be able to log into the browser based management tool at http://127.0.0.1:8080/apex (substituting the port number if you chose something other than 8080).

You probably want to add these few lines to your .bashrc in order to make life easier.

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_SID=XE
export PATH

Start a new shell or source your .bashrc and you should have useful tools such as sqlplus in your path. At this point it’s a good idea to test that your database server is up and running. Connect as SYSDBA using the password you set earlier and enable the basic user ‘HR’:

# sqlplus SYS as SYSDBA
SQL> alter user HR account unlock ;
SQL> alter user HR identified by $password ;
SQL> exit

Now you should be able to connect as user ‘HR’ and query the tables in the sample database:

SQL> select *
2 from countries
3 where country_id = 'UK';

Few programs frustrate me as much as sqlplus however and I’ve come to rely on the developer tool Toad. Happily, there is an open source equivalent to Toad, named TOra. It’s feature rich and uses many of the same key bindings as Toad and I’ve heard there was a shared history between the two applications.

It’s also available in the main Ubuntu repositories. There’s a slight problem however. For legal reasons, these packages haven’t been compiled with Oracle support. Yet with a few tweaks, it’s possible to recompile and package it yourself with Oracle support turned on using the libraries included in the oracle-xe-client package.

There are a couple of things you need to. First, Install packages required for building debs:

# sudo apt-get install build-essential fakeroot

Then download the TOra source and install packages required for building:

# apt-get source tora
# sudo apt-get build-dep tora

Now you need to tweak a few things. Make sure you set your $ORACLE_HOME environment variable, either through your .bashrc, or by doing the following:

# export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

Also, add the Oracle libraries to the paths checked by the linker by creating a file named /etc/ld.so.conf.d/oracle_xe.conf with the following line and running ldconfig:

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib
# sudo ldconfig

Next, you need to edit the file debian/rules in the TOra source package. Change the flags passed to configure to:

--prefix=/usr --with-oracle=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server --without-rpath --disable-new-check --without-kde --enable-libsuffix=

Also comment out the use of the dh_shlibdeps:

# dh_shlibdeps

This turns off shared library dependency checking for packages. This isn’t ideal but it will cause errors when building otherwise. I was a little bit uneasy about this but I came across this article that suggests doing so is sometimes necessary when packaging proprietary apps.

To actually build the package, change to the source package directory and run:

# fakeroot dpkg-buildpackage

It will take a few minutes, depending on your system, but you should see a deb created in the directory above where you ran the build command. You can install it with:

# sudo dpkg -i tora_1.3.22-5_i386.deb

You should now have TOra installed with Oracle support!

I noticed a couple of other things while playing with TOra. I didn’t have MySQL support by default until I installed the package libqt3-mt-mysql. You only need the package installed, there’s no need to re-compile TOra again. I was also struck by how nice it looked. I run Gnome and hardly ever use any Qt apps but I keep meaning to have a look at KDE4 and this was a reminder.

While I was exploring TOra, I also came across a similar project named CrunchyFrog. It’s written in Python and uses GTK. I ran into a couple of bugs but it looks promising. It uses a plugin architecture for handling connections to different databases. In theory you could simply install a plugin to handle connections to Oracle (or MySQL et al). In practice, I could only find a plugin for SQLite but it’s probably the excuse I’ve been waiting for to mess around with it. I got round to reading the manual. I’m still going to check out SQLite though.

All told, the future is bright for open source databases and tools and it’s nice to know that we’ve got the means to operate with Larry’s all pervasive product with a classy desktop app.

Tags: database, linux, mysql, oracle, ubuntu