sql in shell script

Hey I have this shell script that I am trying to run and receive a value from a sql query.Then later use the value in an if stmt... I cannot get this to work for nothing..

Here is the script

(sqlplus -s $usrname/$passwrd <<!
set head off feed off pagesize 0
select b
from table;
set head on feed on pagesize 55
!
) | read b

echo $b

if [ "$b" = "off" ];
then
echo "Bin $msg!"
else
echo "Bin off $msg!"
fi

Hey I have this shell script that I am trying to run and receive a value from a sql query.Then later use the value in an if stmt... I cannot get this to work for nothing..

Here is the script

(sqlplus -s $usrname/$passwrd <<!
set head off feed off pagesize 0
select b
from table;
set head on feed on pagesize 55
!
) | read b

echo $b

if [ "$b" = "off" ];
then
echo "Bin $msg!"
else
echo "Bin off $msg!"
fi

You have to redirect the output to a file using -

spool /app/common/home/nprao/mesg.txt

How would i do that?

try this ...

b=`sqlplus -s $usrname/$passwrd <<!
set head off feed off pagesize 0
select b
from table;
set head on feed on pagesize 55
!`

echo $b

I think this is more what you are looking for:

MyVar=$(
sqlplus -S scott/tiger <<!
set head off feedback off echo off pages 0
select user
from dual
/
!
)
print $MyVar
SCOTT

This only works with a single column and row.

devaris22, please read our rules. Especially:
(4) Do not 'bump up' questions if they are not answered promptly. No duplicate or cross-posting and do not report a post where your goal is to get an answer more quickly.

I have merged your threads.

I have done this and it works, but in my if statement i get back that b is not found. How do i ensure that the value is passed to the if statement

b=`sqlplus -s $usrname/$passwrd <<!
set head off feed off pagesize 0
select b
from table;
set head on feed on pagesize 55
!`

echo $b

My if statement is

if [ $b = "on" ]

Off hand I have two comments. One probably relates to your issue and the other is more style.

1. If your query returns no records then your "if" statement is going to be invalid. Enclose your $b variable with double quotes.
   text if [ "$b" = "on" ]
   
2. Style: You are resetting your SQL*Plus parameters at the tail end of your here document. This doesn't accomplish anything since sqlplus is exiting anyway.

Thanks! it worked....... I guess because i didnt have it in double quotes it does not recognize it as a string.

Thanks a lot!