A lot of my scripting makes use of the 'select' command to create menu driven input. A typical example of how I use it is as:
somevar=''
PS3='Select one: '
while [[ $somevar = "" ]]; do
select somevar in $(sqlplus -s $dbuser/$dbpw@mydb <<EOF
set echo off feedback off verify off head off trimsp on
select colA
from mytable
where colA like 'A%'
order by colA;
EOF
) "None of the above" ; do
if [[ $somevar = "" ]]; then
echo
echo "Please enter a valid number. Retry.";
echo
elif [[ $somevar = "None of the above" ]]; then
exit ;
else {
break ;
}
fi
break
done
done
#
unset PS3
The key point above is that the menu is populated by the result set from a database query, with only one column being selected, so that each row in the resultset is one menu entry.
Now I have a new challenge. I need to select multiple columns, but I still need each complete row to be one menu entry. So that if my SELECT statement becomes
select colA,
colB,
colC
from mytable
where colA like 'A%'
order by colA;
And the result set is
Aaa bbb x x
Abb ccc x y
Acc ddd x z
The menu should be
1) Aaa bbb x x
2) Abb ccc x y
3) Acc ddd x z
The problem is that by default, the 'select' breaks on spaces, and I have spaces between columns of the result, and even within the data of some columns.
Here's the query, run straight up. You can see that each row of the result set is bounded by double-quotes.
SQL> --
SQL> select '"' ||d.dbid ||' '||
2 bp.tag ||' ' ||
3 min(bp.start_time) || ' ' ||
4 min(bs.keep_until) || '"'
5 from rc_backup_piece bp
6 join rc_database d
7 on bp.db_key=d.db_key
8 join rc_backup_set bs
9 on bp.bs_key=bs.bs_key
10 where d.name=upper('&orasid')
11 and bs.keep='YES'
12 group by d.dbid,
13 bp.tag,
14 bs.keep_until
15 order by d.dbid,
16 bp.tag
17 ;
Enter value for orasid: MYDB
old 10: where d.name=upper('&orasid')
new 10: where d.name=upper('MYDB')
'"'||D.DBID||''||BP.TAG||''||MIN(BP.START_TIME)||''||MIN(BS.KEEP_UNTIL)||'"'
---------------------------------------------------------------------------------------------------------------------
"528959692 MYDB_20170228_104831 28-Feb-2017 10:49:11 28-Jun-2017 00:00:00"
"528960845 MYDB_20170228_105831 28-Feb-2017 10:59:14 15-Mar-2017 00:00:00"
2 rows selected.
Elapsed: 00:00:00.04
SQL> spo off
And yet when run in the script, as shown here:
echo Select a backup from the following:
bkupset=''
PS3='Select backup: '
echo " DBID TAG START_TIME KEEP_UNTIL"
while [[ $bkupset = "" ]]; do
select bkupset in $(sqlplus -s $rmanuser/$rmanpw@rmcat <<EOF
set echo off feedback off verify off head off trimsp on
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';
select '"' ||d.dbid ||' '||
bp.tag ||' ' ||
min(bp.start_time) || ' ' ||
min(bs.keep_until) || '"'
from rc_backup_piece bp
join rc_database d
on bp.db_key=d.db_key
join rc_backup_set bs
on bp.bs_key=bs.bs_key
where d.name=upper('$ORACLE_SID')
and bs.keep='YES'
group by d.dbid,
bp.tag,
bs.keep_until
order by d.dbid,
bp.tag
;
EOF
) "None of the above" ; do
if [[ $bkupset = "" ]]; then
echo
echo "Please enter a valid number. Retry.";
echo
elif [[ $bkupset = "None of the above" ]]; then
exit ;
else {
break ;
}
fi
break
done
done
#
unset PS3
We get this result. Notice that the double-quotes themselves become part of the data and we are still breaking on spaces:
Select a backup from the following:
DBID TAG START_TIME KEEP_UNTIL
1) "528959692 8) MYDB_20170228_105831
2) MYDB_20170228_104831 9) 28-Feb-2017
3) 28-Feb-2017 10) 10:59:14
4) 10:49:11 11) 15-Mar-2017
5) 28-Jun-2017 12) 00:00:00"
6) 00:00:00" 13) None of the above
7) "528960845
Select backup:
Close, but still not quite there. It's retaining the first three columns but still breaking off the fourth:
Revised code:
echo Select a backup from the following:
bkupset=''
OLDIFS="$IFS"
IFS="
"
PS3='Select backup: '
echo " DBID |TAG |START_TIME |KEEP_UNTIL"
while [[ $bkupset = "" ]]; do
select bkupset in $(sqlplus -s $rmanuser/$rmanpw@rmcat <<EOF
set echo off feedback off verify off head off trimsp on
alter session set nls_date_format='dd-Mon-yyyy_hh24:mi:ss';
select d.dbid,
bp.tag,
min(bp.start_time),
min(bs.keep_until)
from rc_backup_piece bp
join rc_database d
on bp.db_key=d.db_key
join rc_backup_set bs
on bp.bs_key=bs.bs_key
where d.name=upper('$ORACLE_SID')
and bs.keep='YES'
group by d.dbid,
bp.tag,
bs.keep_until
order by d.dbid,
bp.tag
;
EOF
) "None of the above" ; do
if [[ $bkupset = "" ]]; then
echo
echo "Please enter a valid number. Retry.";
echo
elif [[ $bkupset = "None of the above" ]]; then
exit ;
else {
break ;
}
fi
break
done
done
#
unset PS3
IFS="$OLDIFS"
Result:
Select a backup from the following:
DBID |TAG |START_TIME |KEEP_UNTIL
1) 528959692 MYDB_20170228_104831 28-Feb-2017_10:49:11
2) 31-Mar-2017_00:00:00
3) 528960845 MYDB_20170228_105831 28-Feb-2017_10:59:14
4) 15-Mar-2017_00:00:00
5) None of the above
Select backup: 5
Btw, just to make sure I'm not getting confuse by font rendering on my screen, your suggested change to IFS is
IFS=" < that's IFS equals double-quote sign followed by new line
" < followed by another double-quote sign.
When sqlplus runs the query, it has a default line size of 80 characters, while the data being returned is 81 characters. This causes sqlplus to do a line break just before the column that causes linesize to be exceeded. When I changed the linesize from the default to something a bit larger, allowing all of the data to fit within 'linesize', it all came together.