Link to home
Start Free TrialLog in
Avatar of radical_mit
radical_mitFlag for Switzerland

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/sqlplus / 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....
--------------------------
Avatar of Milleniumaire
Milleniumaire
Flag of United Kingdom of Great Britain and Northern Ireland image

You need to start sqlplus in silent mode and ensure the script you are running doesn't retrieve anything except the required results.  Try this:

echo " Checking for Invalid Object "
NUMBER=`$ORACLE_HOME/bin/sqlplus -s / 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

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.
ASKER CERTIFIED SOLUTION
Avatar of Milleniumaire
Milleniumaire
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of radical_mit

ASKER

thanks, i was just having a brain dead day...