Linux/Unix: How to Store SQL Query Result in a Variable in Shell Script?

The following are two examples of Linux/Unix shell script to store SQL query result in a variable. In the first example, it will store the value in a variable returning single row by the SQL query. And in the second example, it will store the SQL query result in an array variable returning multiple rows.

Linux: Store SQL Query Result in a Variable in Shell Script

In the following example, the SQL query returns the employee's name by passing the employee number, and it will store the value in a variable.

SQL Query Returning Single Row (sqltest.sh)

#!/bin/bash

c_ename=`sqlplus -s SCOTT/tiger@//YourIP:1521/orcl <<END
set pagesize 0 feedback off verify off heading off echo off
  select ename from emp
    where empno = 7566;
exit;
END`

echo "Employee name is $c_ename for employee code 7566."

Make the file executable

chmod +x sqltest.sh

Test

./sqltest.sh

Output

Employee name is JONES for employee code 7566.

The following example is to demonstrate how to store SQL query result into an array returning multiple rows.

sqltest2.sh

#!/bin/bash

i_dept=30

emp_list=`sqlplus -s SCOTT/tiger@//YourIP:1521/orcl <<END
set pagesize 0 feedback off verify off heading off echo off
select ename
  from emp where deptno=$i_dept;
exit;
END`

# print all employee names using for loop
echo "List of Employees in the department $i_dept."
for empname in ${emp_list}
do
  echo $empname
done

Make it executable

chmod +x sqltest2.sh

Test

./sqltest2.sh

Output

List of Employees in the department 30.
WARD
MARTIN
BLAKE
TURNER
JAMES

See also:

• How to Call Oracle Stored Procedure from Shell Script?
• SQL Loader in Unix Shell Script Example