How can i use single quotes for SQL command in shell script

Shell Programming and Scripting
1

Hi.

please help me to write the following query in a shell script.

the Query is :select no,salary from emp_info where name='$var_name'

the following is my code.

#! /bin/sh

var_name=$1

sqlplus -s user/pwd@DB << EOF

select no,salary from emp_info where name="'$var_name'";

exit

EOF

Note: I can use the code for the number comparision.
please help me to get the sigle quotes in that query..

Thankyou all for your valuable answers..

2

Hi little_wonder,

Just remove the double quotes it will work fine in normal case ( where the names wont hav single quotes in it)

 
select no,salary from emp_info where name='$var_name';
3

Hi Panyam,

Thankyou so much for the given solution.

But this i have tried already. but with this i feel a issue that the filed reterived from that query is with spaces and new linr char..

For example when you try to display the result.

#! /bin/sh

var_name=$1

no=`

sqlplus -s user/pwd@DB << EOF

select no,salary from emp_info where name="'$var_name'";

exit

EOF`

echo "No is $no"

The result is like,

no is
123

please give me a solution.

4

Your query is returning name as well as number , but your output shows only number value.

how ever try the following , which is working fine for me.

#! /bin/sh

var_name=$1

op=`sqlplus -s user/pwd<< EOF

set head off
set feed off
set echo off
set line 999


 
 select no,salary from emp_info where name='$var_name';

exit

EOF`

no=`echo $op |awk '{ print $1}'`
salary=`echo $op |awk '{ print $2}'`
5

Hi Panyam,
Thankyou so much..
Its working fine for me..