Using SQL in Shell Scripts

Kyle Gleed and Scott Tarvainen

Introduction

UNIX system administrators are constantly writing shell scripts to automate mundane and repetitive tasks. As UNIX system administrators who are also responsible for database administration, we have incorporated Structured Query Language (SQL) commands into shell scripts to assist us in managing our Relational Database Management System (RDBMS). We use the concept of "Here Documents" (see the sidebar "Here Documents") to embed Oracle SQL*Plus (see the sidebar "SQL*Plus") commands in our Bourne shell scripts.

The Scripts

The two scripts presented here, find_fields.sh (Listing 1) and desc_dbase.sh (Listing 2), demonstrate the benefits of combining SQL and Bourne Shell commands. This approach bypasses the database's front-end application, a practice that is not generally recommended but is often necessary, especially for database administrators.

In troubleshooting database problems, we regularly find ourselves tracking logical relationships within the database tables. Finding these relationships requires multiple describes of the various tables. We developed find_fields.sh to automate this often repetitive task. The second script, desc_dbase.sh, helps us conceptualize the logical layout of our RDBMS -- a necessary precondition to effective troubleshooting.

find_fields.sh

find_fields.sh (Listing 1) uses SQL*Plus commands to identify all tables containing a user-specified field within an Oracle database. After prompting for necessary information to connect to the database and for the appropriate query field, this script identifies all tables within the database that contain the query field. This aids greatly in troubleshooting database relationship problems.

The script begins by prompting the user for appropriate database connection information. The general syntax for SQL*Plus connections is:

sqlplus <DBUSERID>/<PASSWORD>@<DATABASE>

The script then prompts the user for the query field to be searched for in the database tables. It then tries to make the database connection: if successful, it builds a list of all table names within the database. The script next makes a second connection to the database, so that it can search for the user-supplied query field within each of the tables in the database. After completion of the search, the script presents the user with the names of all tables within the database that contain the query field. If no tables were found with the query field, this information is also relayed to the user.

desc_dbase.sh

desc_dbase.sh (Listing 2) uses SQL*Plus commands to describe the table structures of an Oracle database. After prompting for necessary information to connect to the database, this script provides a quick snapshot of what the database looks like (i.e., table names and their field structures). This information is provided in a file the user can either view or print.

This script begins as find_fields.sh did, by retrieving appropriate database connect information. It attempts a database connection and, if successful, builds a list of table names within the database. The script makes a second connection to the database in order to describe each table within it. When the description is complete, the user is given the option of viewing or printing the described database. The print option assumes that the system default printer is set. If it is not, the script should be modified to perform:

lpr -P <PRINTER> /tmp/dbstruct

instead of

lpr /tmp/dbstruct

Both scripts use the procedure error_check to verify database connection status. If an attempted database connection fails, error_check will echo the error condition to the user and exit the script. Errors can occur if the database is shut down, if an invalid connect sequence is initiated, or if the database is empty.

Summary

The concept of embedding SQL commands in shell scripts should prove useful to database administrators, managers, computer security professionals, "power" users, and anyone else concerned with the logical layout of a database(s). These quick, custom-built scripts represent a database management tool too handy to pass up.

Bibliography

Peek, Jerry, Tim O'Reilly, Mike Loukides et al. UNIX Power Tools. Sebastopol, CA: O'Reilly & Associates, 1993. ISBN: 0-679-79073-X

Loney, Kevin. ORACLE DBA Handbook. Berkeley, CA: McGraw-Hill, 1994. ISBN: 0-07-881182-1.

About the Authors

Scott Tarvainen earned a BS in Computer Science from Michigan Technological University and an MS in Education from Troy State University. A computer scientist/system analyst with Command Technologies, Inc., he is currently on contract with the United States Air Force on location at Yokota Air Base, in Japan. Scott has more than seven years of experience in computer systems administration.

Kyle Gleed has an Associate's degree in Computer Information Systems and is completing a BS in Computer Studies with the Universitiy of Maryland. He is currently serving in the US Air Force and is stationed at Yokota Air Base, Japan, where he works as a UNIX system administrator. Kyle has more than six years experience with computer systems and database administration.