How to use select into command in shell script?

I need to get total number of rows in a table by using select count(*) from table and need to assign into a variable in shell script. I used the following script, it does return the number of rows but also with oracle headers, Please help me to eliminate this headers.

Shell Script

#!/bin/sh
count=`sqlplus $username/$password@$tnsname << EOF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
select count(*) from table;
exit;
EOF`
echo "value is "$count

O/P:

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 22 05:01:33 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options 
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 525935 SQL> 
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options

---------- Post updated at 06:28 AM ---------- Previous update was at 06:10 AM ----------

I got it worked by using sqlplus in silent mode

count=`sqlplus -s $username/$password@$tnsname << EOF

Well, that had me flummoxed for a while. There I was about to extol the virtues of the ksh command select in building menus and it's an Oracle question really.

I think what you need to add is the -S flag to your sqlplus command.

I hope that this helps.

Robin
Liverpool/Blackburn
UK

Even if you use -S, I think you will still have the "SQL>" prompts present. Try out -S anyways...

May be this can help?

count=$( `sql...` | sed 's/SQL>//g' )

--ahamed

Hi,
I got the number of records, but when i tried to the count variable in while loop, i have received the below error,
line 23: syntax error near unexpected token `done'
line 23: `done'

#!/bin/sh
outputvar=`sqlplus -S $username/$password@$tnsname << EOF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
select count(*) from table;
exit;
EOF`
while [ $outputvar -gt 0 ] 
do
echo "value is larger"
done

Need to be two lines so that "EOF" is the only contents of the line.

EOF
`

Can't you use if.. ?

[ $outputvar -gt 0 ] && echo "value is larger"  || echo "Not larger.."

OR

if [ $outputvar -gt 0 ]; then
  echo "value is larger" 
else
  echo "Not larger.."
fi

You might want to change it to say:-

typeset -i outputvar=`.....

... so you strip out any white space so you can confidently work with the value rather than hoping that the string returned. Sometimes the tests can be a bit picky depending how you phrase them and your distribution.

I hope that this helps

Robin
Liverpool/Blackburn
UK

Still I'm getting the same error after moving the EOF` to separate lines.

Below is the error when i used if condition suggested by you,

line 25: syntax error near unexpected token `fi
line 25: `fi'

I am a newbie to shell scripting. Please help.

Can you paste the code you have used?
Also, echo the variable $outputvar... I have a feeling, there might be a newline in it...

Probably you can paste the output of sh -x <your script>

--ahamed

#!/bin/sh
outputvar=`sqlplus -S $username/$password@$tnsname << EOF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
select count(*) from table;
exit;
EOF
`
echo "value is "$outputvar
if [ $outputvar -gt 0 ]; then
  echo "value is larger" 
else
  echo "Not larger.."
fi

O/p

value is  52365
test.sh: line 25: syntax error near unexpected token `fi'
test.sh: line 25: `fi'

I have got the same error when i tried with while loop as well.

Paste the output of sh -x <your script> , that should help!

--ahamed

Please find the same here

++ sqlplus -S $'dev/dev\orcl'
' outputvar='    52365
+ echo 'value is ' $'52365\r\r'
value is  52365
test.sh: line 25: syntax error near unexpected token `fi'
test.sh: line 25: `fi'

There is a carriage return! Try this...

outputvar=`echo $outputvar | sed 's/\r//g' `
if [ ...

--ahamed

Still the same error. Please find the modified code and o/p

#!/bin/sh
outputvar=`sqlplus -S $username/$password@$tnsname << EOF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
select count(*) from table;
exit;
EOF
`
outputvar=`echo $outputvar | sed 's/\r//g' `
echo "value is "$outputvar
if [ $outputvar -gt 0 ]; then
  echo "value is larger" 
else
  echo "Not larger.."
fi

O/P

' outputvar='    52365
++ echo $'52365\r'
++ sed 's/\r//g'
+ outputvar=$'52365\r'
' echo 'value is 52365
value is 52365
test.sh: line 26: syntax error near unexpected token `fi'
test.sh: line 26: `fi'

Try this instead...

outputvar=`echo $outputvar | sed 's/\\\r//g' `

--ahamed

I think your script has become corrupt through editing with a Microsoft Editor such as Notepad.

Just to prove the theory, please post the output from this "sed" command which is designed to make control codes visible:

sed -n l scriptname

We are looking for spurious '\r' (carriage-return) characters.
To get rid of them and create a new script:

cat scriptname | tr -d '\r' > newscriptname

Created the new script using the below

cat scriptname | tr -d '\r' > newscriptname

It worked perfectly. Cool