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.

The problem that he was grappling with was how to exchange data between a unix shell script and the database.

Unfortunately for Simon, I don’t know much about Teradata other than how to spell it. However, there are, as ever, a number of incredibly useful unix features which, when combined with SQL*Plus, make this sort of thing possible in the Oracle world without all that mucking around with precompilers.
So, leaving the pub, the remainder of this post will be how to :

  • Invoke sqlplus from a shell script and do something in the database
  • Get a value out of the database for use in a shell variable
  • Pass a shell variable in for use in a SQL statement
  • Pass a filename in on the command line for SQL*Plus to execute
  • Use WHENEVER SQLERROR to return an exit code to the script

Note – I’ve written and tested all of these examples on Ubuntu (using the bash shell) against Oracle XE. They should all work in other unix shells ( korn, bourne, csh, look – just pick your favourite) and against other versions of Oracle.
Oh, and I’ve used different syntax to do the same thing in the different scripts just to illustrate the various ways of doing the same thing.
To connect to oracle from the $ prompt, you’d normally type :
or even
sqlplus uid/pwd@db
if you want to connect without being prompted for a username and password.

There are a couple of further arguments we can provide to sqlplus which are especially useful in this context – the silent switch, which suppresses the banner you normally get when you connect to the database…
sqlplus -s
and the name of a script file to be executed on login :
sqlplus uid/pwd@db @scriptfilename.sql

Invoking SQL*Plus in a Shell Script

Time to make like Doris Stokes and see if we can receive messages from the Other Side ( other side, OS, geddit ?)
Let’s start by creating a table :

CREATE TABLE other_side_messages(
    message VARCHAR2(4000),
    message_ts TIMESTAMP)

Now let’s write a script which does something in the OS, connects to the database and runs some SQL, before returning control back to the shell script.

# connect to the database and insert a record into a table
echo 'Doing stuff.....'
echo 'doing more stuff....'
echo 'connecting to database'
sqlplus -s username/password@database <<- END_SCRIPT
    INSERT INTO other_side_messages( message, message_ts)
    VALUES( 'Greetings from the OS', SYSDATE);
    exit 0;
echo 'Doing more OS stuff...'
echo 'Done.'

Where username/password@database is your username password and database.

This script uses a shell construct called Here Document. The <<- followed by the label delimits the commands that SQL*Plus is going to run. The matching label at the end tells the script where the SQL*Plus commands end.
NOTE – the ending label needs to be on a separate line and NOT have spaces before or after it.
As unix has a habit of never saving files with execute permissions, we need to set this ourselves before running it :

mikes@mikes-laptop:~$ chmod 744
mikes@mikes-laptop:~$ . ./
Doing stuff.....
doing more stuff....
connecting to database

1 row created.

Commit complete.

Doing more OS stuff...

Now if we check the table :

FROM other_side_messages
MESSAGE                   MESSAGE_TS  
------------------------- --------------------------------
Greetings from the OS     14-JUN-10

Not bad I suppose, but it could be a bit more secure. We don’t really want to hard-code a uid/pwd string if we can help it. Also, what if we want to go to the database and get a value to use in the shell script ?

Returning the result of a Query into a shell variable

Look, it’s a post about shell scripting, at some point you’re going to have to use some obscure character on the top row of your keyboard :

# Shell script to get a value from a database query and then display it
# Script takes a uid/pwd@connect_str as an argument
if [ $# -lt 1 ]; then
    echo 'Usage is uid/password@connnect_string'
    exit 1
echo 'Calling Database.'
msg=`sqlplus -s $1 <<- END_SCRIPT
    set heading off
    SELECT message
    FROM other_side_messages;
echo $msg
exit 0

The ` character is the key next to the 1 on the top row ( on a standard UK keyboard anyway).
Well that’s a bit more like it. We can get information out of the database to use in the shell script. Let’s run it…

mikes@mikes-laptop:~/dbscripts/here_doc$ . ./ user/pwd@db
Calling Database.
Greetings from the OS

Next up :

Passing a shell variable into a SQL statement

At this point I got a bit bored with the whole medium analogy and decided to have a random side-swipe at any passing SQL IDE. In case you’re not a regular visitor to this blog (hello Mum…again), you can see a thoroughly scientific and in-depth comparison between SQL Developer and TOAD here.

# Script to use a shell script variable in a SQL statement
if [ $# == 0 ]; then
    echo 'Usage is uid/pwd@connect_str'
    exit 1
msg='I laugh at your puny IDE'
echo 'Calling Database'
sqlplus -s $1 <<- INSERT_SCRIPT
    INSERT INTO other_side_messages( message, message_ts)
    VALUES( '$msg', SYSDATE);
echo 'That told them.'

It doesn’t have to be a SQL statement, you can do anything that you can normally do in SQL*Plus. Of course you don’t have to use here document…

Passing a filename to execute

First off, here’s the script we’re going to pass in

INSERT INTO other_side_messages( message, message_ts)
VALUES( 'Is that Derek Acorah ?', SYSDATE);
exit 0;

In this example, we’ve saved the script in the same directory as the script, but you can call it from anywhere, as long as you specify the path in the call

# run a script file by passing the name in the call to sqlplus
if [ $# == 0 ]; then
    echo 'Usage : uid/pwd@connect_str'
    exit 1
sqlplus -s $1 @ins_message.sql
echo 'Done'

That’s all well and good. But what if something goes horribly wrong ? After all, any self-respecting program you call from a shell script will have the good taste and manners to provide an exit value. Well, SQL*Plus can do the same.
First off, here’s a script which isn’t going to work…unless you have a rather unusual table naming convention

set heading off
set termout off
FROM my_silly_table;
exit 0;

and now, the shell script :

# this looks a lot like the last script except this calls silly.sql
if [ $# == 0 ]; then
    echo 'Usage : uid/pwd@connect_str'
    exit 1
sqlplus -s $1 @silly.sql
if [ $? == 0 ]; then
    echo 'Script ran OK'
    exit 0
    echo 'Script has passed over to the Other Side'
    exit $?

If we now run the shell script and we get an error back from SQL*Plus…

mikes@mikes-laptop:~/dbscripts/here_doc$ . ./ uid/password@db
Script has passed over to the Other Side

All of which goes to show, it’s amazing what you can learn in the pub.

2 thoughts on “Getting the Quiz Machine to Pay Out – talking to your database from a shell script

  1. Pingback: Do You Need a Website or a Web Designer? | Effective Affiliate Strategies

  2. Pingback: Chirpir News | Getting the Quiz Machine to Pay Out – talking to your database …

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.