How to Get SQLPLUS Query Output to a Shell Script Variable

Most of the time during the shell scripting we get requirement to get the database table query result values to a shell script variables. Here is one example to get a single column values to a variable. If you have multiple column outputs then we will have to concatenate into a single column and pass it to the shell script variable. You can use awk or scripting commands to make it separate.

Example: In this example I am counting the data pump jobs into a shell variable called IMP_ACTIVE.

IMP_ACTIVE=`sqlplus -s scott/tiger << !!

 set pages 0 line 130 serveroutput on feedback off

 select count(*) from dba_datapump_jobs where job_name='AUD' and state='EXECUTING';

 exit;

 !!`

 N1COUNT=`sqlplus -s / << !!

 set pages 0 line 130 serveroutput on feedback off

 $N1SQL

 exit;

 !!`