Solved

Outputting SQL text into KSH variable and acting on i

Posted on 2011-03-03
3
641 Views
Last Modified: 2013-12-27
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....
--------------------------
0
Comment
Question by:radical_mit
  • 2
3 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 35025978
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.
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 35025995
Sorry, just noticed that your unix syntax is also wrong.  Environment variables are case sensitive and you need to reference them by using the $ prefix:

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

Does sqlplus run from your script?  If not, then you need to setup your Oracle Environment by setting ORACLE_SID and ORACLE_PATH etc.
0
 

Author Closing Comment

by:radical_mit
ID: 35026094
thanks, i was just having a brain dead day...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now