Help with 'select' for menu input


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help with 'select' for menu input
# 1  
Old 03-01-2017
Help with 'select' for menu input

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:

Code:
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

Code:
select colA,
          colB,
          colC
from mytable
where colA like 'A%'
order by colA;

And the result set is
Code:
Aaa             bbb             x x
Abb             ccc             x y
Acc             ddd             x z

The menu should be
Code:
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.

Is there any solution?
# 2  
Old 03-01-2017
Could you try to enclose each line (= select item) in double quotes?
# 3  
Old 03-01-2017
Quote:
Originally Posted by RudiC
Could you try to enclose each line (= select item) in double quotes?
No joy.

Here's the query, run straight up. You can see that each row of the result set is bounded by double-quotes.
Code:
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:
Code:
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:
Code:
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:

# 4  
Old 03-01-2017
Try changing the value of IFS to newline, so select (and shell syntax in general) only splits output on lines:

Code:
OLDIFS="$IFS"
IFS="
"

You'll want to do IFS="$OLDIFS" whenever not in that loop to prevent splitting from acting strange later.
This User Gave Thanks to Corona688 For This Post:
# 5  
Old 03-01-2017
Try also double quoting the sqlplus command substitution.
# 6  
Old 03-02-2017
Quote:
Originally Posted by Corona688
Try changing the value of IFS to newline, so select (and shell syntax in general) only splits output on lines:

Code:
OLDIFS="$IFS"
IFS="
"

You'll want to do IFS="$OLDIFS" whenever not in that loop to prevent splitting from acting strange later.

Close, but still not quite there. It's retaining the first three columns but still breaking off the fourth:

Revised code:

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:

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

In vi, with line numbering on, it looks like this

Code:
131 IFS="
132 "

# 7  
Old 03-02-2017
That's right.

If select is breaking on that column, I suspect it has a newline in it.
This User Gave Thanks to Corona688 For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Stuck with menu in select

hi i am new to bash scripting .. i created a bunch of folders but only want the folder names with file1. so i go in and make an array to grab the folders the put in a file then i strip the directories so i just have the folder names i need then i would like to make the menu with a selection... (3 Replies)
Discussion started by: lamby22
3 Replies

2. Shell Programming and Scripting

Format of 'select' generated menu

Oracle Linux 5.6 64-bit Given the below snippet ORACLE_SID='' PS3='Select target (test) database being refreshed: ' # while ]; do select ORACLE_SID in `egrep -i '^FS|^HR' /etc/oratab |\ awk -F\: '{print $1}'|sort` ; do if ]; then echo echo "Please enter a... (19 Replies)
Discussion started by: edstevens
19 Replies

3. Shell Programming and Scripting

File Select Menu Script

Hi All, I need to develop a bash script list “list of files” and able to select if any and set as globe variable in script and use for other function. I would like to see in menu format. Example out put Below are the listed files for database clone 1. Sys.txt 2. abc.txt 3. Ddd.txt... (1 Reply)
Discussion started by: ashanabey
1 Replies

4. Shell Programming and Scripting

Select ksh menu question

I am creating a Select menu with a few options and I would like to create a "better" looking interface than just this: 1) Option 1 2) Option 2 3) Option 3 Instead, I would like something like this: *********** * Cool Script * * 1) Option 1 * * 2) Option 2 * * 3) Option 3 *... (2 Replies)
Discussion started by: chipblah84
2 Replies

5. Shell Programming and Scripting

reprint the select menu after a selection

Hi, I am using a select in ksh for a script #!/bin/ksh FIRSTLIST='one two three four quit' PS3='Please select a number: ' select i in $FIRSTLIST ; do case $i in one) print 'this is one' ;; two) print 'this is 2' ;; three) print 'this is 3' ;; four) print... (7 Replies)
Discussion started by: omerzzz
7 Replies

6. Shell Programming and Scripting

Error using select menu command

Hi All, I am trying to use the select command & the menu. below mention is my script #!/bin/bash 2 3 PS3="Is today your birthday? " #PS3 system variable 4 5 echo "\n" 6 7 8 select menu_selection in YES NO QUIT 9 do 10 11 ... (1 Reply)
Discussion started by: milindb
1 Replies

7. Shell Programming and Scripting

Select command to build menu

Hello everyone. I am using the select command to build a menu, here is my question: Is it possible to generate a menu which contains several sections and have a separator between the sections without having a selection number generated in front of the separator? This is a sample of what I would... (1 Reply)
Discussion started by: gio001
1 Replies

8. Shell Programming and Scripting

reappearing menu list using select

is there a way I can make the menu list reappear when I use select ? ----- menulist="Change_title Remove_tag Change_tag Add_line Quit" select word in $menulist #change_title remove_tag change_tag add_line quit do case $word in # first menu option Change Title ... (9 Replies)
Discussion started by: forever_49ers
9 Replies

9. Shell Programming and Scripting

Drop down menu in bash for timezone select

Is there any way to implement a drop down menu selection in bash? This is on CDLinux which is a very minimal live CD and I am using it to install an image onto a hard drive. Part of that process is the timezone selection. There are just too many timezones to attempt to use the "select" command.... (1 Reply)
Discussion started by: simonb
1 Replies

10. Shell Programming and Scripting

dynamic Select menu

Hi all is menu driven by SELECT can be a dynamic ? My requirement is that i want SELECT to be created on run time not predefine . The select should be created as per the no of words in a file thanks in advance rawat (2 Replies)
Discussion started by: rawatds
2 Replies
Login or Register to Ask a Question

Featured Tech Videos