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/[email protected]//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/[email protected]//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