Use a shell variable in where clause

Shell Programming and Scripting
1

Hi all,

I want to use a variable inside my sql query and below is my script:

 
#!/bin/ksh
export b="abcd"
a=`sqlplus -s abc/def@ghi <<++
set heading off;
set feedback off;
select xxx from mytable where clmn_nm='$b';
exit;
++`
echo $a

But the output i get is below:

 
$> sh sqltry.sh

 
$>

I am not able to figure out why the query is not taking the variable's value.

I even tried double quotes in the where clause, but no use.

Any help appreciated.
Thanks in advance!!

2

The reason are the single quotes you put around the variable. These prevent the expansion of variables:

# x="fubar"
# echo $x
fubar
# echo '$x'
$x

If you want to strip the special meaning of the single quotes off them you have to quote (=escape) them:

# echo \'$x\'
'fubar'

I hope this helps.

bakunin

3

well.. try removing export and also make sure the select query is returning a value.. apart from this i cant see any issues with your script..

---------- Post updated at 06:45 PM ---------- Previous update was at 06:35 PM ----------

Hi Bakunin,

Single quote wont do any harm in side sqlplus..

 
SQL> select ID from users where LOGINNAME='TALLYMAN';
        ID
----------
         0
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
gold0813*TPCRCST2>b="TALLYMAN"
gold0813*TPCRCST2>a=`sqlplus -s TALLYMAN/******** <<EOF
> set head off;
> set feedback off;
> select ID from users where LOGINNAME='$b';
> exit;
> EOF`
gold0813*TPCRCST2>echo $a
0
gold0813*TPCRCST2>
4

You are right - not in SQLPLUS. In the here-document, which is interpreted by the shell, though ....

bakunin

5

1) Single-quotes won't prevent variable expansion/evaluation in the here-doc. It's like using single quotes within double-quotes:

$ name='ELIXIR'

$ while read i
> do
>  echo $i
> done <<END
> FIRST LINE
> a='$name'
> BYE
> END
FIRST LINE
a='ELIXIR'
BYE

.

2) One can prevent the shell from interpreting the here-doc content by quoting/escaping the label (whole label or a single character):

$ echo $name
ELIXIR
$ while read i
> do
>  echo $i
> done <<\END
> FIRST
> a='$name'
> BYE
> END
FIRST
a='$name'
BYE

.