How to use select into command in shell script?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to use select into command in shell script?
# 1  
Old 12-22-2011
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
Code:
#!/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:
Code:
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


Moderator's Comments:
Mod Comment How to use code tags


---------- 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

Last edited by Franklin52; 12-22-2011 at 07:16 AM.. Reason: Please use code tags for code and data samples, thank you
# 2  
Old 12-22-2011
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
# 3  
Old 12-22-2011
Even if you use -S, I think you will still have the "SQL>" prompts present. Try out -S anyways...

May be this can help?
Code:
count=$( `sql...` | sed 's/SQL>//g' )

--ahamed
This User Gave Thanks to ahamed101 For This Post:
# 4  
Old 12-22-2011
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'


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

# 5  
Old 12-22-2011
Quote:
EOF`
Need to be two lines so that "EOF" is the only contents of the line.
Code:
EOF
`

# 6  
Old 12-22-2011
Quote:
Originally Posted by vel4ever
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'

Code:
while [ $outputvar -gt 0 ] 
do
echo "value is larger"
done

Can't you use if.. ?
Code:
[ $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

# 7  
Old 12-22-2011
You might want to change it to say:-
Code:
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Modifying the shell script to select pattern

Hello, I have script which work 70% of the desired task , the output from script.sh is following , however the desired output I require is following . Any piece of suggestion would be great.. thanks in advance, emily #!/bin/bash ... (8 Replies)
Discussion started by: emily
8 Replies

2. Shell Programming and Scripting

Select answers from multiple questions using shell script

I have a text file in this format Some lines.... Question no: 1 The question? A. Answer 1 B. Answer 2 C. Answer 3 D. Answer 4 Answer:B Some lines.... Question no: 2 The question? (choose 2) (10 Replies)
Discussion started by: zorrox
10 Replies

3. Shell Programming and Scripting

Select command going to infinite loop after running the script

cd /opt/et/WAS/apps/8.0 find . -name "HostIntegration.properties" -o -name "HostSocket.properties" -o -name "environment.properties" 2> /dev/null | awk -F '' '{print $4}'|awk '!x++' | cat>/home/cbadmin/file1.txt cd /home/cbadmin/ PS3='Please enter a number from list of applications==>:' select... (3 Replies)
Discussion started by: bhas85
3 Replies

4. Linux

How to execute a simple select script using a shell script?

Hi team, I have two select statements and need to run them using SYSDBA user select * from temp_temp_seg_usage; select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb from dba_segments where segment_name='TEMP_TEMP_SEG_USAGE'; Need to run this using a shell script say named... (1 Reply)
Discussion started by: pamsy78
1 Replies

5. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

6. Shell Programming and Scripting

How to print the output of a select query using shell script?

HI, I want to connect to database and fetch the count from a table. The sql query is as below : select count(*) from table_test where test_column='read'; How can I print the output of this statement using shell script. Thanks in advance. (4 Replies)
Discussion started by: confused_info
4 Replies

7. Shell Programming and Scripting

Select combination unique using shell script

Hi All, bash-3.00$ gzgrep -i '\ ExecuteThread:' /******/******/******/******/stdout.log.txt.gz <Jan 7, 2012 5:54:55 PM UTC> <Error> <WebLogicServer> <BEA-000337> < ExecuteThread: '414' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "696" seconds working on the request... (4 Replies)
Discussion started by: osmanux
4 Replies

8. Shell Programming and Scripting

sql select command output formatting in shell script

Hi, I need to connect to the database and retrieve two variables from the database and store them in a variable,out of these two variables I need to get lastdigit appended to the variable 1 retrieved and variable 2 with out any modification in short select var,data from usage; o/p=... (1 Reply)
Discussion started by: rkrish
1 Replies

9. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

10. Shell Programming and Scripting

Supress ' quotes in a select statement inside Shell Script

Hi I have a shell script in which a string variable is saving following query. SqlQuery="select a||'|'||b||'|'||c from dual" I am trying to pass above query as a parameter to some script but its giving me error for "single quote". I tried suppressing it using \' instead of just ' but it... (1 Reply)
Discussion started by: mahabunta
1 Replies
Login or Register to Ask a Question