Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Outputting SQL text into KSH variable and acting on i

Posted on 2011-03-03
3
Medium Priority
?
648 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
[X]
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
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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

715 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