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
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:
#!/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
column <name> noprint
before the select statement, this way the function only returns the tablespace_name column.