Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Storing the value of a single query to a Variable - What am i doing wrong?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Storing the value of a single query to a Variable - What am i doing wrong?
# 1  
Old 08-20-2014
Storing the value of a single query to a Variable - What am i doing wrong?
I tried the below :

Code:
 
#!/bin/ksh
testvar=$(
sqlplus $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
COMMIT;
EXIT;
EOF)
echo "testvar="$testvar

The output that I am getting is :
Code:
 
> sh example.sh
+ sh example.sh
testvar=

Honestly, what am I doing wrong ?? Kindly help!

Cheers.
# 2  
Old 08-20-2014
try
Code:
#!/bin/ksh
testvar=`sqlplus $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
COMMIT;
EXIT;
EOF`
echo "testvar="$testvar

# 3  
Old 08-20-2014
COMMIT and EXIT are not needed. You need the commit statement to "confirm" data manipulation statements like INSERT, UPDATE, DELETE,...
SQL*Plus should be called in silent mode so you do not get its banner in your output.
The end of your here document should be the only characters in that line, put the closing brace in the next line.
You specify a shell in the shebang but call the script using sh. To my knowledge the shebang is ignored when you call a script like this.
Code:
testvar=$(
sqlplus -s $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
EOF
)
echo "testvar="$testvar

# 4  
Old 08-21-2014
Many thanks for all replies folks.

But i finally figured out what was wrong. The correct code is :

Code:
 
#!/bin/ksh
testvar=$(
sqlplus $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
COMMIT;
EXIT;
EOF)
echo "testvar=$testvar"

I should have enclosed the
Code:
$testvar

in double quotes, as in
Code:
"$testvar"

Again many thanks for all the inputs.

Cheers.Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Passing variable as input & storing output in other variable

I have a below syntax its working fine... var12=$(ps -ef | grep apache | awk '{print $2,$4}') Im getting expected output as below: printf "%b\n" "${VAR12}" dell 123 dell 456 dell 457 Now I wrote a while loop.. the output of VAR12 should be passed as input parameters to while loop and results... (5 Replies)
Discussion started by: sam@sam
5 Replies

2. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

3. Shell Programming and Scripting

bash assign mysql query single field to variable

I'm running a bash script query and assigning the output to a variable like this: exists=`mysql -u $USER_NAME --password=$PASSWORD -D "somedb" \ -e "SELECT * FROM somedb.sometable WHERE field1 ='$a' \ AND field2 ='$b' LIMIT 0 , 30";` which returns something like: echo... (2 Replies)
Discussion started by: unclecameron
2 Replies

4. Shell Programming and Scripting

perl storing single value from file

Hi, I have a configuration file that contains one word that I want to store as a variable to use later in my perl program. i.e. #cat /opt/dti/ssh_user root I want to store root as a variable in perl. In bash I would do this as user=$(cat /opt/dti/ssh_user) (1 Reply)
Discussion started by: borderblaster
1 Replies

5. Shell Programming and Scripting

About storing the value of wc -l into a variable and then using this value in while

Hi all, I m new to this forum. I ma facing onei issue. I have something like this: length= wc -l < b2| awk '{print $1}' where b2 is filename having detauls like: cat b2 abc1 abc4 xyc3 sbdghf4 but when I do echo "$length" it displays nothing Also I am using awk to overcome... (4 Replies)
Discussion started by: student2009
4 Replies

6. Shell Programming and Scripting

add the output of a query to a variable to be used in another query

I would like to use the result of a query in another query. How do I redirect/add the output to another variable? $result = odbc_exec($connect, $query); while ($row = odbc_fetch_array($result)) { echo $row,"\n"; } odbc_close($connect); ?> This will output hostnames: host1... (0 Replies)
Discussion started by: hazno
0 Replies

7. Shell Programming and Scripting

Storing value in a variable

Hi Everyone, I have a code which requires to be stored in different variables and I am achiving it like this. HOST=`echo $RMP | cut -f2 -d:` NAME=`echo $RMP | cut -f3 -d:` DIR=`echo $RMP | cut -f4 -d:` TYPE=`echo $RMP | cut -f5 -d:` Is there any other way of storing value... (2 Replies)
Discussion started by: gehlnar
2 Replies

8. Shell Programming and Scripting

Problem while storing sql query value in a variable

Hi, When i execute the below statement , the value is not getting stored in the variable. AnneeExercice=`sqlplus $LOGSQL/$PASSWORDSQL << FIN >> $GEMOLOG/gemo_reprev_reel_data_ventil_$filiale.trc SELECT bi09exercice FROM bi09_scenario WHERE bi09idfiliale=UPPER('de') AND ... (1 Reply)
Discussion started by: krishna_gnv
1 Replies

9. Shell Programming and Scripting

Storing a variable?

I'm writing a bash shell script to backup several mysql databases. This script will run on a daily basis and send a copy to a remote FTP repository. The filenames are in the format DATE.backup.sql. How do I store the DATE variable so I can delete/move/etc the file on the FTP server the next time... (4 Replies)
Discussion started by: hoover90
4 Replies

10. Shell Programming and Scripting

storing database query in a variable

I am executing a SQL query in Unix Server: select status,location from user_information where request_id='DS-43720' In shell script I am writing this query in the below manner: echo "select status,location from user_information where request_id='DS-43720' ;" >> $directory/info.sql ... (3 Replies)
Discussion started by: debu
3 Replies
Login or Register to Ask a Question