Today we’re going to see how to connect and run queries on a database from a UNIX bash script.

There are various reasons why a need will rise to connect the DB from the UNIX Shell – for instance running a hot backup script which will be scheduled in the crontab.

The following script will ask for database user credentials and will check if those credentials are a valid user login to the Oracle Database. It’s probably not that useful as is, but shows basic script that uses sqlplus.

#!/bin/bash

# exit codes
CRED_ERR=1  # if the credentials are not valid
NOARGS=2    # if the required parameters were not supplied

echo Please enter an Oracle Username:
read USERNAME

echo "Please enter the Oracle Username's Password:"
read –s PASS    # -s attribute will not print the password on the screen.

SID=${ORACLE_SID}

if [ -z "${USERNAME}" ] || [ -z "${PASS}" ];   # Exit if no arguments were given.
then
echo "Error: Username or Password are empty"
exit $NOARGS
fi ;

sqlplus -s -l $USERNAME/$PASS@$SID  << EOF

exit
EOF

errorCode=$?    # checks if the last operation (sqlplus) was completed successfully or not
if [ ${errorCode} -ne 0 ]
then
echo "SQLPlus was unable to connect the DB with the supplied credentials"
exit ${CRED_ERR}
else
echo  "SQLPlus was connected successfully"
fi

A more useful script will access the database and will return various values from the Database according to our needs.
The following script will connect the Database and will print the file names of a specific tablespaces.
One can run this script by running “file_name.sh <tablespace_name>”

#!/bin/bash

# exit codes
GEN_ERR=1  # something went wrong in the script
NOARGS=2    # if the required input arguments were not supplied

if [ $# -ne 1 ];    # check number of input arguments
then
echo "Usage: $0 <TABLESPACE_NAME>"
exit ${NOARGS}
fi

# Transforming the parameter to uppercase
TABLESPACE_NAME=$(echo $1 | tr [:lower:] [:upper:])

# get the file names into FILE_LIST variable
FILE_LIST=$(sqlplus -s -l / as sysdba <<EOF
set echo off heading off feadback off
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = '${TABLESPACE_NAME}';
exit;
EOF)

if [ $? -ne 0 ]
then
echo "Running sqlplus FAILED"
exit ${GEN_ERR}
fi

# print the file list
if [ -z "${FILE_LIST}" ] # check if FILE_LIST is empty
then
echo "No Datafiles were found in the tablespace '${TABLESPACE_NAME}'"
else
echo "The datafiles of '${TABLESPACE_NAME}' are:"
echo "${FILE_LIST}"
fi