LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 11-14-2021, 08:37 PM   #1
pp1979
LQ Newbie
 
Registered: Nov 2021
Posts: 2

Rep: Reputation: Disabled
Shell Script - Help with variable in SQLPLUS


I have a variable that returns 3 fields but in same line as below :

#####################
[grid@hostname ~]$ failgroup_names=$($ORACLE_HOME/bin/sqlplus -s "/ as sysasm" <<EOF
> set feedback off
> set heading off
> set pagesize 0
> set head off lines 200 feedback off timing off serveroutput on
> select distinct FAILGROUP from v\$asm_disk where name not like 'QD_%' ;
> exit
> EOF
> )

[grid@hostname ~]$ echo failgroup_names
failgroup_names
[grid@hostname ~]$ echo $failgroup_names
FAILGROUP04 FAILGROUP05 FAILGROUP06
[grid@hostname ~]$
#####################

I need to use these 3 values and run query below:


####################
flgs=`IFS=$'\n'; for i in $failgroup_names; do echo $i; done `

echo "Failure groups are: "
echo "$flgs"


for flgsn in "${flgs[@]}"
do

split=`IFS=$'\n'; for i in $flgs; do echo $i; done `
num_distinct_disks=$($ORACLE_HOME/bin/sqlplus -s "/ as sysasm" <<EOF
set feedback off
set heading off
set pagesize 0
set head off lines 200 feedback off timing off serveroutput on
SELECT count(1) num_disks
FROM V\$ASM_DISK d, V\$ASM_DISKGROUP dg
WHERE d.group_number = dg.group_number
AND dg.name in ('DATAC2')
and d.name not like 'QD%'
and d.FAILGROUP="$split". --- HERE I HAVE TO ADD ONE OF 3 VALUES AT A TIME. IT IS NOT WORKING
GROUP BY dg.name, d.failgroup, d.state, d.header_status, d.mount_status,
d.mode_status;
exit
EOF
)

echo "$split" | tr " " "\n"
# echo "$flgsn"
# echo "$flgs[1]"
echo $num_distinct_disks
done


####################

I tried adding "" , adding '', adding ''' ''', nothing works.

Can someone help ?
 
Old 11-14-2021, 09:26 PM   #2
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_12{.0|.1}
Posts: 5,777
Blog Entries: 23

Rep: Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785Reputation: 3785
Welcome to LQ and the Programming forum!

Please wrap your code and data snippets inside [CODE]...[/CODE] tags. Doing so will preserve indentation and provide other visual clues which make it easier for others to comprehend. You may write those yourself as shown, or use the # button available with Advanced edit options. (A complete list of BBCode tags is always available via a link near the bottom of every thread view).

I am unfamiliar with SQLPlus itself, but in general in SQL query syntax if you need to select records where some column has one of several values you might structure the query like this...

Code:
SELECT columns specification
FROM table specification
WHERE column_name IN(list of values)
So in your example...

Code:
WHERE ...
and d.FAILGROUP="$split". --- HERE I HAVE TO ADD ONE OF 3 VALUES AT A TIME. IT IS NOT WORKING
... would become...

Code:
WHERE ...
and d.failgroup IN($split_list)
... where split_list would usually be a comma separated list of quoted values.

So you would probably need to change the value of your split variable to be that comma separated, individually quoted list of values, something like split = " 'FAILGROUP04','FAILGROUP05','FAILGROUP06' " (I have added extra spaces to make the quoting visible).

Last edited by astrogeek; 11-14-2021 at 09:28 PM.
 
Old 11-15-2021, 09:11 AM   #3
pp1979
LQ Newbie
 
Registered: Nov 2021
Posts: 2

Original Poster
Rep: Reputation: Disabled
In my case I need to have omething like this :

and d.FAILGROUP='FAILGROUP4'. (how to add ' and one variable value at a time here?)

I know I could have an array, hot to use array value but in SQL will need the failgroup='$arrayvalue1'

tks
 
Old 11-15-2021, 09:27 AM   #4
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 4,224
Blog Entries: 1

Rep: Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585Reputation: 1585
IN operator will do.
Something like this:
Code:
failgroup_names='alfa beta gamma'
insert_this_into_sql=$(printf "'%s'," $failgroup_names | sed 's/^/(/;s/,$/)/')
echo  $insert_this_into_sql
output:
Code:
('alfa','beta','gamma')
 
1 members found this post helpful.
Old 11-15-2021, 10:16 AM   #5
shruggy
Senior Member
 
Registered: Mar 2020
Posts: 3,037

Rep: Reputation: Disabled
^The same with a Bash array:
Code:
failgroup_names=(alpha beta gamma)
IFS=,
echo "(${failgroup_names[*]@Q})"

Last edited by shruggy; 11-15-2021 at 10:21 AM.
 
2 members found this post helpful.
Old 11-16-2021, 10:07 AM   #6
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,852

Rep: Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111Reputation: 3111
As you appear to be connecting to the same database and as the same user both times, why not just incorporate it all into a single script?

You may need to check your group by, but assuming what you have is correct, maybe something like:
Code:
set feedback off
set heading off
set pagesize 0
set head off lines 200 feedback off timing off serveroutput on
 
select vad.failgroup
     , count(1) num_disks
  from v$asm_disk      vad
     , v$asm_diskgroup vadg
 where vad.group_number = vadg.group_number
   and vadg.name        = 'DATAC2'
   and vad.name  not like 'QD%'
GROUP BY vadg.name
       , vad.failgroup
       , vad.state
       , vad.header_status
       , vad.mount_status
       , vad.mode_status;

Last edited by grail; 11-16-2021 at 08:54 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Make shell script to write entry into DB using oracle Sqlplus and then exit. kkrrss Linux - Server 3 01-22-2017 11:20 AM
Problems with storing oracle sqlplus query output shell script rockf1bull Red Hat 0 12-04-2011 12:47 PM
Want to learn/use SQLPLUS in shell script saurabhmehan Linux - Newbie 1 09-15-2010 03:08 AM
sqlplus using shell script arfal Programming 3 02-08-2010 09:52 PM
pass variable from one shell script into another shell script xskycamefalling Programming 9 10-03-2009 02:45 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 11:46 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration