radical_mit
asked on
Outputting SQL text into KSH variable and acting on i
Hello, I am trying to log into a Oracle database, and according to the output of the select run a second sql command that fixed the issue, then go onto the next command.
the shell I SQL I am trying to write is:
----
echo " Checking for Invalid Object "
NUMBER=`$ORACLE_HOME/bin/s qlplus / as sysdba << /var/tmp/com2.sql
echo " number:= $NUMBER"
if [ number -ne 0 ]then;
$ORACLE_HOME/bin/sqlplus / as sysdba << /var/tmp/com21.sql
fi
----
where /var/tmp/com2.sql is:
select count(1) from dba_objects where status="INVALID" ;
------------------------
where
var/tmp/com21.sql is the coomand that fixes the status....
--------------------------
the shell I SQL I am trying to write is:
----
echo " Checking for Invalid Object "
NUMBER=`$ORACLE_HOME/bin/s
echo " number:= $NUMBER"
if [ number -ne 0 ]then;
$ORACLE_HOME/bin/sqlplus / as sysdba << /var/tmp/com21.sql
fi
----
where /var/tmp/com2.sql is:
select count(1) from dba_objects where status="INVALID" ;
------------------------
where
var/tmp/com21.sql is the coomand that fixes the status....
--------------------------
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, i was just having a brain dead day...
echo " Checking for Invalid Object "
NUMBER=`$ORACLE_HOME/bin/s
echo " number:= $NUMBER"
if [ number -ne 0 ]then;
$ORACLE_HOME/bin/sqlplus / as sysdba @/var/tmp/com21.sql
fi
The /var/tmp/com2.sql should contain the following:
set heading off feedback off pages 0
whenever sqlerror exit failure
select count(1) from dba_objects where status="INVALID" ;
exit
The com21.sql script should also include an exit at the end.