Bash for DBAs – Part 2

In this post I’ll continue talking about writing bash scripts for database stuff. I’ll devote this entire part to sqlplus, as there is a lot to say here.

Running sqlplus from bash

I’ll start with the simplest thing, running sqlplus from bash. sqlplus can get several flags, the most important one for scripting is the “-s” one which is silent. With this flag sqlplus will not show the banner and prompt, so the output will be clean.
When writing scripts, I believe in a single script that does everything (most of the time). That way the script is very portable, we don’t care where we run it from, etc. I’ve seen many bash scripts that use a separated file for SQL commands, which they run using sqlplus (i.e. running “sqlplus user/pass @script.sql”). Because I don’t like extra files, I prefer to write the SQL commands in the bash script itself. To do that I use bash input redirection (“<<“). The double “less than” should be followed by a label (the standard is EOF, but we can have any label we want). Then we simply write all of our SQL commands and they are considered as input to sqlplus. After the sqlplus commands we will write the label at the beginning of a new line, and this will terminate the input redirection. A nice thing that I learned is using the “<<-” instead (adding a minus sign after the double “less than”). The minus tells bash that the label that terminates the input redirection can be indented using tabs (not spaces). This is very cool as without the minus, the label must be at the beginning of the line and it messes the entire indentation of the script.
Note that in the examples in this post I didn’t indent the termination label as it seems that the code plugin doesn’t support tabs. I preferred to keep the label at the beginning of the line instead of having spaces which will actually break the script if you copy and paste it.
One last thing here, we should exit sqlplus before ending the input. It doesn’t do any harm if we don’t, but it kills the sqlplus aggressively and it’s not the best practice.
It should look something like that:

$ORACLE_HOME/bin/sqlplus -s / as sysdba <<- EOF
   startup
   exit
EOF

sqlplus exit code

When we execute commands in sqlplus, it is important to know if they were successful or not. To do that we can use another sqlplus feature: “whenever sqlerror”. Using the “whenever sqlerror” clause we can tell sqlplus to exit with a specific exit code whenever it gets an SQL error. Then we can use this exit code in the script. In this example I show startup failure, but it works on any SQL error including “connect”.

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 21 15:32:09 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> whenever sqlerror exit 1
SQL> startup
ORACLE instance started.
Total System Global Area  636100608 bytes
Fixed Size       1366500 bytes
Variable Size    188745244 bytes
Database Buffers 440401920 bytes
Redo Buffers     5586944 bytes
ORA-00205: error in identifying control file, check alert log for more info
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
$ echo $?
1

Getting sqlplus output

Another very common thing to do is to take information from the database and use it in the script. To do that I will use sqlplus as I explained above and assign the output into a variable.
Several things to remember:

• Use “sqlplus -s” so the variable will not contain the banner and other stuff
• Use the “set” command to format the output (depends on your query, I usually set “lines”, “pages” and “feedback”
• You might want to change column format using the “col” command
• Make sure that there are no column headers by setting “pages” to 0 (zero) or “set heading off”
• If you have any special bash characters (like $) escape them using backslash (\)

Example:

$ cat tmp.sh
#!/bin/bash
status=$($ORACLE_HOME/bin/sqlplus -s /nolog <<- EOF
   whenever sqlerror exit 1
   connect / as sysdba
   set pages 0 lines 200 feedback off
   select status from v\$instance;
   exit
EOF
)
if [ $? -eq 0 ]; then
   echo "status is '${status}'"
else
   echo "Something went wrong"
   exit 1
fi
$ ./tmp.sh
status is 'OPEN'

Using bash variables in sqlplus

We often need to use some bash variables in sqlplus. Well, this is really easy, we just use the variables as we would in the script (i.e. ${var}). Once we have the dollar sign ($) the bash expands the variable (that’s why we have to use backslash (\) if we actually need the dollar sign).

Summary

In this post I showed how to run sqlplus from bash with several important options. Now we can actually use these tools to write great scripts. In the next (and last) post of this series I’ll post a demo script which includes most of the things I talked about here.