OS: Oracle Linux 5.10
DB Oracle 11.2.0
I need to query the database and assign the return value to a shell variable, but it's not working.
create table imr_env (key varchar2(1000), value varchar2(1000) ;
insert into imr_env values('TblspcUsagePct','90');
commit;
Here is the shell script:
#!/bin/bash
echo "in script"
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 ; export ORACLE_HOME
ORACLE_SID=IMR1 ; export ORACLE_SID
export PATH=$PATH:$ORACLE_HOME/bin
pct=`sqlplus -S app/manager <<END
set serveroutput on
declare
output_val number ;
BEGIN
select value into output_val from imr_env where key = 'TblspcUsagePct' ;
dbms_output.put_line('output_val: ' || to_char(output_val)) ;
END ;
/
exit;
END`
## another sqlplus connection, use $pct in the where clause
echo "var value is $pct"
Here is the output:
SQL> ./test.sh
in script
var value is
output_val: 90
Why isn't the shell variable being populated with the value retrieved from the database?
I need to use $pct in another query that will tablespaces that are at least 90% full.