How to use select into command in shell script?

Login or Register to Ask a Question and Join Our Community

How to use select into command in shell script?

Tags

shell oracle, shell scripts

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to use select into command in shell script?

12-22-2011

Registered User

121, 1

Join Date: Dec 2011

Last Activity: 7 July 2014, 8:51 PM EDT

Location: Chennai, India

Posts: 121

Thanks Given: 41

Thanked 1 Time in 1 Post

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

vel4ever

View Public Profile for vel4ever

Find all posts by vel4ever

12-22-2011

Moderator

3,843, 841

Join Date: Jun 2007

Last Activity: 29 June 2020, 12:30 PM EDT

Location: Lancashire, UK

Posts: 3,843

Thanks Given: 2,004

Thanked 841 Times in 727 Posts

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

rbatte1

View Public Profile for rbatte1

Visit rbatte1's homepage!

Find all posts by rbatte1

12-22-2011

Registered User

1,910, 488

Join Date: Sep 2008

Last Activity: 22 December 2019, 2:31 AM EST

Location: San Jose, CA

Posts: 1,910

Thanks Given: 54

Thanked 488 Times in 481 Posts

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:

ahamed101

View Public Profile for ahamed101

Find all posts by ahamed101

12-22-2011

Registered User

121, 1

Join Date: Dec 2011

Last Activity: 7 July 2014, 8:51 PM EDT

Location: Chennai, India

Posts: 121

Thanks Given: 41

Thanked 1 Time in 1 Post

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

vel4ever

View Public Profile for vel4ever

Find all posts by vel4ever

12-22-2011

Registered User

6,402, 678

Join Date: Mar 2008

Last Activity: 8 June 2016, 9:58 PM EDT

Posts: 6,402

Thanks Given: 288

Thanked 678 Times in 647 Posts

Quote:

EOF`

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

Code:

EOF
`

methyl

View Public Profile for methyl

Find all posts by methyl

12-22-2011

Registered User

125, 3

Join Date: Mar 2011

Last Activity: 25 July 2017, 4:47 AM EDT

Location: India / Kerala

Posts: 125

Thanks Given: 24

Thanked 3 Times in 2 Posts

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

linuxadmin

View Public Profile for linuxadmin

Find all posts by linuxadmin

12-22-2011

Moderator

3,843, 841

Join Date: Jun 2007

Last Activity: 29 June 2020, 12:30 PM EDT

Location: Lancashire, UK

Posts: 3,843

Thanks Given: 2,004

Thanked 841 Times in 727 Posts

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

rbatte1

View Public Profile for rbatte1

Visit rbatte1's homepage!

Find all posts by rbatte1

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

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)

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

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

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

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.

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

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

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

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

Login or Register to Ask a Question