Shell Script - Help with variable in SQLPLUS
by pp1979 from LinuxQuestions.org on (#5RXKQ)
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 ?
#####################
[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 ?