Getting the Quiz Machine to Pay Out – talking to your database from a shell script

I was in the pub the other day with my mate Simon. It’s surprising just how many of my posts have their genesis in such a setting. For the benefit of any prospective employers ( and my Mum), I put this down to the company and the mental stimulation of working out just exactly how you collect your winnings from the Quiz Machine.
For anyone who does not have first-hand experience of English Pub Quiz machines, the trick is either to a) get the barman to pay you from the till or b) have about your person a rather large hammer.

Fortunately, the hammer wasn’t required on this particular occasion, which is just as well as neither of us had brought one ( it’s not really that kind of pub). The reason for our lack of success eventually became apparent. After a conversational odyssey through the Bedfordshire countryside (the vicissitudes of Luton Town) via Table Mountain (England’s prospects for the World Cup), Simon – definitely the brains of the operation in Quiz Machine terms – confessed to wrestling with one of those perennial problems that are an occupational hazard of the Database Specialist’s art.

Yes, as well as being a bit of a whizz on the Science and Nature stuff, Simon is a long-time Teradata expert. Continue reading

Mostly Painless – Upgrading to Ubuntu 10.04

I’m still not quite sure how, but I’ve managed to acquire a second laptop. It’s been sat there for a while, happily running Ubuntu 9.10 but not really doing too much else, so when the opportunity to migrate to the latest and greatest Ubuntu version came along, it just seemed like a good idea to re-christen it “Gullible Guinea Pig” and introduce it to “Lucid Lynx”. Continue reading

Troubleshooting Oracle 10g XE Installation on Ubuntu

You’ve followed the instructions, everythings working fine. You shutdown your machine and go for a celebratory pint / cup of tea / curry.
Then, when you next start your machine, Oracle doesn’t want to know. What’s going on ?
To be honest, I don’t know. What I do know is that there are some steps you can take to at least attempt to diagnose your problem.

Before I go any further, I’d like to dedicate this post to Wayne.
He found himself in this position. After much head scratching between the two of us, he managed to find and resolve his problem.

Whilst a hardened veteran in computing terms, Wayne was new to both Linux and Oracle. From the mails I’ve received regarding this post, there seem to be a number of people in a similar position to him. Therefore, this post is written with these interpid souls in mind and I’ve tried to take time out to explain why we’re doing stuff at each step.

Update

It’s now quite a while since I posted this and some people have contributed some really useful comments. These include solutions that they found to various problems, which are additional to the ones in this post. So, if you’re still having problems, check out the comments as well. MestreLion’s tip about sudo chmod -t /var/tmp/.oracle seems to be a particularly good example of this.

One other point worth mentioning is that Oracle 11gXE is now available. Many of the issues in this post are still relevant. However, if you’d like to know how to install the latest XE on Ubuntu or Mint, here are the instructions
Continue reading

Tunneling X over SSH on Ubuntu 9.10

“Just a second”, you’re thinking, “I thought this blog was supposed to be about Oracle stuff ?”
This is true…broadly speaking. However, I’ve spent a fair chunk of the last week playing with Ubuntu 9.10 server working up to putting Oracle on it. This particular mini-adventure will come in handy when I come to do the actual install as Oracle uses a graphical interface as it’s main installation tool.

So, this tuneling X over SSH sounds pretty impressive. Tuneling itself sounds very technical, a fact augmented by the inclusion of a TLA in the phrase. And the X just serves to make it sound rather mysterious and exciting.

As is so often the case in such matters, the truth is rather more prosaic. Continue reading

Essaying a sashay with Samba

Just in case either of my kids are reading this, don’t panic. I’ve not suddenly decided to break the habit of a lifetime and indulge in a bit of Dad Dancing. No, this is a vain attempt to improve my geek-cred, rather than my street-cred. As promised in my last post, this is a wander through my adventures in configuring Samba to share files from my shiny new Ubuntu 9.10 Server installation.
Continue reading

Installing Ubuntu Server and SSH setup

It is said that the Devil makes work for idle hands. I’ve got a week off. I know, I’ll have a crack at setting up an Ubuntu server on a desktop PC.
Note to self – I really must get a life. Apparently they can be quite useful.

Right now, however, it’s probably a good idea to set out what I’m trying to achieve here.

This server is just something for me to play around with at home. I don’t need to worry too much about large amounts of traffic.
I want to keep my holiday snaps and my music on it so I can access them from the other machines in my house.

I also want to install Oracle 11g on it at some point, so I have somewhere to play with an up-to-date version of Oracle.
As 11g tends to get a bit sniffy if it’s got a GB or less of physical memory to play with, I need to make sure that the host machine has enough memory to accommodate it.

I’ll post about the Samba setup and Oracle installation separately. For now, I want to concentrate on the initial Server installation and SSH setup. Continue reading

Customising gedit for PL/SQL on Ubuntu 9.10

As I’ve said before, when it comes to doing serious Database programming, I prefer to work in SQL*Plus.

Yes, SQLDeveloper ( and Toad for that matter) are great for looking at stored program units, running ad-hoc queries etc, but I find there’s no substitute for being “close to the metal” when it comes to in-depth or complex programming in PL/SQL.

Apart from anything else, the feedback you get at the prompt is the actual error, and not masked by something the IDE is objecting to.
Line numbering also helps a lot. If the compiler reports an error at line 100, I want to be able to go to line 100 in my code directly, no messing. Text editors tend to do this stuff really well.

At work, being stuck on Windows, I have to rely on the trusty Textpad, which is perfectly adequate.
Fortunately, when I get home, it’s Ubuntu all the way. This means getting to play with another of those unobtrusive Linux gems – gedit. Continue reading

Solved – The Mystery of SQLDeveloper and the Missing ocijdbc11

This is a follow up to my earlier post about SQLDeveloper being moody and suddenly refusing to connect to a database via TNS.
Having had a bit of a dig around, it would seem that this problem is not platform specific and affects Windows in the same way.
At this point, I’d like to say a big “thank you” to Grzegorz Wilczura, who referred me to this article by Sue Harper.
If you’re hitting this problem on Windows, then you may want to follow the instructions there to set up a TNS_ADMIN environment variable.

Remember, this problem had two primary symptoms :-

  1. Empty Network Alias list when defining a TNS connection
  2. When testing an existing TNS connection you get :
    Status: Failure – Test failed : no ocijdbc11 in java.library.path

In Sue’s article, it states that SQLDeveloper looks for a tnsnames.ora in the following places in this order :

  • $HOME/.tnsnames.ora
  • $TNS_ADMIN/tnsnames.ora
  • /etc/tnsnames.ora
  • $ORACLE_HOME/network/admin/tnsnames.ora

Only one of these places is has an absolute path. The rest all rely on environment variables being set. However, when I run SQLDeveloper from the Ubuntu desktop menu, I’m not starting a shell, so my .bashrc doesn’t get executed. Therefore, these variables are not set.
When I setup my first tns connection, I’d just installed sqldeveloper and ran it by executing the shell script ( sqldeveloper.sh) from a Terminal Window. Of course, the $ORACLE_HOME was set in this environment and SQLDeveloper could therefore see the tnsnames.ora in $ORACLE_HOME/network/admin.

All of this means that the cause of the problem is that SQLDeveloper cannot see, or can no longer see, the tnsnames.ora file.

Copying the tnsnames.ora to /etc will fix the problem. However, probably the best solution is to ensure that we’re only referencing one tnsnames.ora and don’t replicate it. That way, we only ever have to change it in one place, should the need arise.

So, the alternative I’ve chosen is to set the $ORACLE_HOME environment variable in sqldeveloper.sh – the script that gets called to start SQLDeveloper.
Start a terminal and go to the SQLDeveloper home directory ( in my case, I installed SQLDeveloper in /opt) :-

cd /opt/sqldeveloper
sudo gedit sqldeveloper.sh

Now amend the file so it looks something like this :

#!/bin/bash
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

Now re-start SQLDeveloper from the Ubuntu menu. Remember, this menu item is simply executing the shell script we’ve just changed.
If you have an existing tns connection defined then you can test doing the following :

  1. Right-click the connection and select Properties from the pop-up menu.
  2. This will bring up the New/Select Database Connection window.
  3. Enter the password in the Password field
  4. Hit the test button.

The test should now succeed.

If you haven’t got a TNS connection defined currently, you should now be able to test by setting one up, with no problem.

Playtime…watching DVDs on Ubuntu 9.10

It’s not all sad geeky database type stuff here you know. Sometimes, I just want to kick back and watch a movie.
For some ( apparently legal) reasons, stuff like DVD and Flash doesn’t work on Ubuntu out of the box.
To access a veritable cornucopia of goodies, you need to do the following :

sudo apt-get install ubuntu-restricted-extras
sudo /usr/share/doc/libdvdread4/install-css.sh

Additionally, if you want to be able to access a DVD’s menu, you’ll need the totem-xine package :

sudo apt-get install totem-xine

Full details can be found in the Ubuntu Community Documentation on Restricted Extras – which is how they refer to this stuff.
I thought it was worth a post here because it’s something I stumbled across and, speaking to fellow Ubuntu ingenues it seems that it’s one of those things that isn’t particularly well publicised.