Shell Programming and Scripting

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:

Code:

#!/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

You now have an array: arr

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

Code:

#!/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

If you need to know ahead of time what the number of the choice will be (it's assigned automatically by the 'select' statement, you will have to make sure you can use the sql statement to properly order your results with 'order by'. You can use a different column than "tablepsace_name" to 'order by' if you select it also, but use a

Code:

column <name> noprint

before the select statement, this way the function only returns the tablespace_name column.