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
doneMake it executable
chmod +x sqltest2.sh
Test
./sqltest2.sh
Output
List of Employees in the department 30. WARD MARTIN BLAKE TURNER JAMES
What if employee name has spaces?
example: 'Will Smith'
In this case, the for loop will split it as two strings, Will and Smith.. How to handle this?