Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Use a shell variable in where clause

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Use a shell variable in where clause
# 1  
Old 09-20-2012
Use a shell variable in where clause
Hi all,

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

Code:
 
#!/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:

Code:
 
$> 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  
Old 09-20-2012
Quote:
Originally Posted by Jayaraman
I am not able to figure out why the query is not taking the variable's value
The reason are the single quotes you put around the variable. These prevent the expansion of variables:

Code:
# 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:

Code:
# echo \'$x\'
'fubar'

I hope this helps.

bakunin
# 3  
Old 09-20-2012
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 ----------

Quote:
Originally Posted by bakunin
The reason are the single quotes you put around the variable. These prevent the expansion of variables:

Code:
# 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:

Code:
# echo \'$x\'
'fubar'

I hope this helps.

bakunin
Hi Bakunin,

Single quote wont do any harm in side sqlplus..

Code:
 
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  
Old 09-20-2012
Quote:
Originally Posted by vidyadhar85
Single quote wont do any harm in side sqlplus..
You are right - not in SQLPLUS. In the here-document, which is interpreted by the shell, though ....

bakunin
# 5  
Old 09-20-2012
Quote:
Originally Posted by bakunin
You are right - not in SQLPLUS. In the here-document, which is interpreted by the shell, though ....

bakunin
1) Single-quotes won't prevent variable expansion/evaluation in the here-doc. It's like using single quotes within double-quotes:
Code:
$ 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):
Code:
$ echo $name
ELIXIR
$ while read i
> do
>  echo $i
> done <<\END
> FIRST
> a='$name'
> BYE
> END
FIRST
a='$name'
BYE

.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How can I assign awk's variable to shell script's variable?

I have the following script, and I want to assign the output ($10 and $5) from awk to N and L: grdinfo data.grd | awk '{print $10,$5}'| read N L output from gridinfo data.grd is: data.grd 50 100 41 82 -2796 6944 0.016 0.016 3001 2461. where N and L is suppose to be 3001 and 100. I use... (8 Replies)
Discussion started by: geomarine
8 Replies

2. Shell Programming and Scripting

Generating & executing the SELECT and UPDATE clause dynamically using shell script

Hi All, I need to write one shell script. The requirement is as follows. a) I am having list the employee names in a file stored in /tmp directory location as below /tmp/emp.txt b) and the contents are as below cat emp.txt ravi raj ram arun c) I need to connect to sybase... (1 Reply)
Discussion started by: Gowtham_giri
1 Replies

3. Shell Programming and Scripting

[SHELL: /bin/sh] For loop using variable variable names

Simple enough problem I think, I just can't seem to get it right. The below doesn't work as intended, it's just a function defined in a much larger script: CheckValues() { for field in \ Group_ID \ Group_Title \ Rule_ID \ Rule_Severity \ ... (2 Replies)
Discussion started by: Vryali
2 Replies

4. Shell Programming and Scripting

Check a variable value through if clause

Hi guys, I am trying to check the values i have for two variables. if && ; then echo "Success"; fi Now Test1 can have any Alpha Variable and Count is a integer value. Even though we have given 'and' Condition, even one condition is sucess, i am getting the Success message. ... (11 Replies)
Discussion started by: mac4rfree
11 Replies

5. Shell Programming and Scripting

Not able to store command inside a shell variable, and run the variable

Hi, I am trying to do the following thing var='date' $var Above command substitutes date for and in turn runs the date command and i am getting the todays date value. I am trying to do the same thing as following, but facing some problems, unique_host_pro="sed -e ' /#/d'... (3 Replies)
Discussion started by: gvinayagam
3 Replies

6. Shell Programming and Scripting

assign awk's variable to shell script's variable?

Dear All, we have a command output which looks like : Total 200 queues in 30000 Kbytes and we're going to get "200" and "30000" for further process. currently, i'm using : numA=echo $OUTPUT | awk '{print $2}' numB=echo $OUTPUT | awk '{print $5}' my question is : can I use just one... (4 Replies)
Discussion started by: tiger2000
4 Replies

7. Shell Programming and Scripting

clause for setting a variable to $1 if it exists

Hi all, What is the simplest way to setting a variable to $1 if it exists ? If I go with name=${"$1":-abc}, bash complains "bad substitution", So I use name="$1" name=${name:-abc} But is there a way to fix this "bad substitution" ? Thanks! (2 Replies)
Discussion started by: qiulang
2 Replies

8. Shell Programming and Scripting

Bash-Shell: If-Clause to check if file is empty

Hello, I want to checkl whether my file has text in it or not. if ; then ... if ; then ... But none of these work Can someone help me? ---------- Post updated at 09:00 AM ---------- Previous update was at 08:55 AM ---------- The code-tags caused an displayerror,... (5 Replies)
Discussion started by: ABE2202
5 Replies

9. UNIX for Dummies Questions & Answers

if clause

hi, pls could you help me with one program in KSH ( i have sunOS). I need to create an If clause, that prints an error message and filenames, when in a directory are found some files of null size (find . -type f -size 0 ). thanks (3 Replies)
Discussion started by: palmer18
3 Replies

10. Shell Programming and Scripting

building a SET clause in shell script

Hi, I have a comma delimited string, e.g. empno, ename, sal. Using Korn Shell Script I want to build the SET clause for an UPDATE statement, and set clause should look like this: empno=decode(:empno, '?', empno, :empno), ename=decode(:ename, '?', empno, :ename), sal=decode(:sal, '?',... (14 Replies)
Discussion started by: shalua
14 Replies
Login or Register to Ask a Question