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 :
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…
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.
#!/bin/sh # # 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); COMMIT; exit 0; END_SCRIPT 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 hd1.sh mikes@mikes-laptop:~$ . ./hd1.sh Doing stuff..... doing more stuff.... connecting to database 1 row created. Commit complete. Doing more OS stuff... Done.
Now if we check the table :
SELECT * FROM other_side_messages / MESSAGE MESSAGE_TS ------------------------- -------------------------------- Greetings from the OS 14-JUN-10 22.30.39.000000 SQL>
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 :
#!/bin/sh # # 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 hd2.sh uid/password@connnect_string' exit 1 fi echo 'Calling Database.' msg=`sqlplus -s $1 <<- END_SCRIPT set heading off SELECT message FROM other_side_messages; quit END_SCRIPT` 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$ . ./hd2.sh 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.
#!/bin/sh # # Script to use a shell script variable in a SQL statement # if [ $# == 0 ]; then echo 'Usage is hd3.sh uid/pwd@connect_str' exit 1 fi 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); COMMIT; exit; INSERT_SCRIPT 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); COMMIT; 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
#!bin/sh # run a script file by passing the name in the call to sqlplus # if [ $# == 0 ]; then echo 'Usage : hd4.sh uid/pwd@connect_str' exit 1 fi 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 WHENEVER SQLERROR EXIT FAILURE SELECT * FROM my_silly_table; exit 0;
and now, the shell script :
#!bin/sh # this looks a lot like the last script except this calls silly.sql # if [ $# == 0 ]; then echo 'Usage : hd5.sh uid/pwd@connect_str' exit 1 fi sqlplus -s $1 @silly.sql if [ $? == 0 ]; then echo 'Script ran OK' exit 0 else echo 'Script has passed over to the Other Side' exit $? fi
If we now run the shell script and we get an error back from SQL*Plus…
mikes@mikes-laptop:~/dbscripts/here_doc$ . ./hd5.sh 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.