Outputting SQL text into KSH variable and acting on i

Posted on 2011-03-03
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
where /var/tmp/com2.sql is:
select count(1) from dba_objects where status="INVALID" ;
var/tmp/com21.sql  is the coomand that fixes the status....
Question by:radical_mit
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 16

Expert Comment

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

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" ;

The com21.sql script should also include an exit at the end.
LVL 16

Accepted Solution

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 ]
     $ORACLE_HOME/bin/sqlplus / as sysdba @/var/tmp/com21.sql

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

Author Closing Comment

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

634 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