Press "Enter" to skip to content

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/[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

See also:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *