How do I dynamically assign the below output to unix shell variables
so I can build a menu in a shell script?

Example:

var1 = 1 var2= SYSTEM
var3 = 2 var4= UNDOTBS1

and so on, then in the shell script I can use the variables to build a menu.




set serveroutput on

declare
v_counter number(2):=0;
v_tbs dba_tablespaces.tablespace_name%type;

cursor tbs_cur is
select tablespace_name
from dba_tablespaces
where tablespace_name not in ('TEMP');

begin
open tbs_cur;
dbms_output.put_line('Number'||' '||'Tablespace Name');
loop
exit when tbs_cur%notfound;
fetch tbs_cur into v_tbs;
v_counter := v_counter+1;
exit when tbs_cur%notfound ;
dbms_output.put_line(v_counter||' '||v_tbs);
end loop;
close tbs_cur;
end;
/


SQL> /
Number Tablespace Name
1 SYSTEM
2 UNDOTBS1
3 SYSAUX
4 USERS
5 MAINT
6 MAINT_IDX
7 OFP
8 OFP_IDX
9 TMX
10 TMX_IDX

PL/SQL procedure successfully completed.

one way:

#!/bin/ksh

sqlplus -s user/pswd@somedb <<EOF > outputfile.lis
set serveroutput on

declare
v_counter number(2):=0;
v_tbs dba_tablespaces.tablespace_name%type;

cursor tbs_cur is
select tablespace_name
from dba_tablespaces
where tablespace_name not in ('TEMP');

begin
open tbs_cur;
dbms_output.put_line('Number'||' '||'Tablespace Name');
loop
exit when tbs_cur%notfound;
fetch tbs_cur into v_tbs;
v_counter := v_counter+1;
exit when tbs_cur%notfound ;
dbms_output.put_line(v_counter||' '||v_tbs);
end loop;
close tbs_cur;
end;
/
exit
EOF

while read num val
do
   arr[$num]="$val"
done < outputfile.lis

When I want to dynamically create menus in shell scripts from data in an Oracle db I do something like the following:

#!/bin/ksh

function getTabsp {
  sqlplus -S <logon/pass> <<_EOD
  set echo off
  column tablespace_name format a30
  set feedback off
  set heading off
  set pagesize 0
  select tablespace_name 
  from dba_tablespaces
  where tablespace_name not in ('TEMP');
  exit
_EOD
}

PS3="Which Tablespace? "
select tabsp in $(getTabsp)
do
  if [[ -z $tabsp ]]; then
    <error processing here>>
  else
     <$tabsp equal the user's choice of tablespace names>
     <$REPLY equals the number of that choice>
     <do whatever you need with those values>
  fi
done

column <name> noprint