Pulling information from Oracle table and assigning it to a UNIX variable

Let us say you have a table (myemployeetable) containing employee names, IDs etc and you are writing a script carrying a variable meant to hold the total number of employees. You can get the total count of employees by running a sql file and assigning the output to this variable.

This is however not that straightforward. A sql command outputs a lot of information that has to be excluded when the result value is assigned to the variable.

First, you don't want to print column names, so turn the heading off.
set heading off

Next, in the output, you do not want messages like "x records selected". To do this, turn feedback off.
set feedback off

Suppress all headers, page breaks, titles etc by setting the page size to 0.
set pagesize 0

If you are using a SQL variable, you have to suppress how a variable is substituted before being sent to execution. This is done by turning verify off.
set verify off

The output of sql command shows "SQL>", this can be suppressed by turning echo off.
set echo off;


So your sql file, say getemployeecount.sql, will look like this:
set heading off
set feedback off
set pagesize 0
set verify off
set echo off;

select count(*) from myemployeetable;

exit

When you execute this from sql prompt, you will get the number of employees returned.

When a script has to execute the sql file, it will first enter sqlplus, which returns banner and the initial SQL prompt to the user. we don't need that either. Therefore, sqlplus is called "silently" using -S option.

Now we assign the value of the total number of employees to a UNIX variable using command substitution.

SUMEMP=`sqlplus -S user/pass @getemployeecount.sql`