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 ?

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

So in your example...

... would become...

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

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

IN operator will do.
Something like this:
output:

1 members found this post helpful.

^The same with a Bash array:

2 members found this post helpful.

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: