Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Outputting SQL text into KSH variable and acting on i

Posted on 2011-03-03
3
Medium Priority
?
649 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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

782 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