How to redirect value from sql select statment to unix variable

Shell Programming and Scripting
1

Hi,

I need to get the value from the table using the sql command and store this value into the unix variable so that i can use this value for furthure use..

Please can any body help me in this regards

Thanks & Regards
Abdul Hafeez Shaik

2

Something like :

 mysql -h localhost -P 33060 -u root -p password \"select status from MyData where size=1000 > tomyfile.txt

? What do you mean by variable ?

3

Thanks for the replay,

I want to get the out put of the select statment into a variable,

export v_sys =0
export v_che =0

After delcaring i want get the out put of the follwing select statment into v_sys variable

select 6 from dual

Thanks & Regards
Abdul Hafeez Shaik

4

use this

v_syse=`${ORACLE_HOME}/bin/sqlplus -s user_id/password <<END
select 6 from dual ;
EXIT;
END`

--Manish Jha

5

Thank you Manish,

I will try this in my script and let you know.

Regards
Abdul Hafeez Shaik

6

Hi All

I have tried the following script but am not getting any output, can any one help me to know the problem in my script

export v_test=0
v_test='sqlplus -s apps/apps <<END
set feedback off
set heading off
select 6 from dual;
EXIT;
END'
echo out put is $v_test

Thanks & Regards
Abdul Hafeez Shaik

7

v_test=$(sqlplus -s apps/apps <<END
set feedback off;
set heading off;
select 6 from dual;
EXIT;
END)

8

hi,

I have tried this script but this time i did not got the expected result, instead am getting the output diffrently

when i ran the follwoing script

export v_test=0
v_test=$(sqlplus -s apps/apps <<END
set feedback off
set heading off
select 6 from dual;
exit;
END)
echo out put is $v_test

then the output is :

sh test.sh
out put is Usage: SQLPLUS [<option>] [<user>[/<password>] [@<host>]] [@<startfile> [<parm1>] [<parm2>] ...] where <option> ::= { -s | -? } -s for silent mode and -? to obtain version number

but i was expecting:

out put is 6

can you please let me know what to do

9

Hi,
Iam not pretty sure,can u able to run the commands separately.
first sqlplus -s apps/apps like that

10

how about this

sqlplus -s apps/apps <<END
set feedback off
set heading off
select 6 from dual;
exit;
END
v_test=$?
echo output is $v_test
11

Hi andryk,
the variable v_test=$? wont catch the value 6 it will catch only the return status of the previous command.

12

tell what error u got? did u check your sql separatly if it is working correct??
I have so many scripts which does like this .. I dont know what you are doing wrong?..

v_syse=`${ORACLE_HOME}/bin/sqlplus -s user_id/password <<END
select '6' from dual ;
EXIT;
END`

13

:eek: sorry, didnt look at the question!!!
But why dont you redirect the result to a file and work from there

sqlplus -s apps/apps <<END
set feedback off;
set head off;
spool /tmp/somefile.result;
select 6 from dual;
exit;
END
echo output is 
cat /tmp/somefile.result
rm /tmp/somefile.result
14

NLS=`${ORACLE_HOME}/bin/sqlplus -s<<EOF
/ as sysdba
set heading off
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
EOF`

echo $NLS

works fine here

15

Hello abdul,

you have used ' instead of ` in the above code.
look for the above symbol above tab button in keyboard .. :slight_smile:

16

dude,

why not just redirect the output to a file first so you can analyze what is wrong with the output. since from experience when you perform sqlplus there are some garbage which you do not need. then start from there.

sqlplus -s apps/apps <<endsql > output.txt
set feedback off
set heading off
select 6 from dual;
exit;
endsql

or

sqlplus -s apps/apps <<endsql
set feedback off
set heading off
select 6 from dual;
spool output.txt;
r;
exit;
endsql

also from experience sometimes when you put the query into script it will give you no output. so try to spool the output then you can start from there or either way try them both then filter out the garbage so you can have what you want. a log file is also good for troubleshooting but if you do not like you can just redirect the result to a variable.

hope this helps.

17

$Test_Var=
`sqlplus -s $USERNAME/$PASSWD@$DATABASE <<START
set echo off;
set head off;
select 10 from dual;
exit;
START | tr -d " " `

Output
$echo $Test_Var
10
$

18

Hi Team,

I have tried code to get the value from oracle. but it is not working. The error shows invalid identifier. but if i execute same query in oracle SQL*PLUS it is working fine.

v_test=$(sqlplus -s apps/apps <<END
set feedback off;
set heading off;
select c_code from tab_code where c_name = 'NAM';
EXIT;
END)