Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help with 'select' for menu input

Login to Discuss or Reply to this Discussion in Our Community

 
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:
edstevens
# 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:
edstevens
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