Executing a SQL query from a shell script

98_1LE · #1 (**permalink**) 03-22-2002

I cannot figure out how to run a SQL script, or just a sqlplus query, from a shell script (bash or ksh). Basically, I need to su - oracle from root and run a query, then test the exit status.

#2 (**permalink**) 03-22-2002

You want a script to run as root and launch a script in oracle? I think the cleanest way is two separate shell scripts. I tried an embedded su thru an endword (which I do a lot), but I could not capture the status code inside the su - pretty strange.

myroot.sh:

Code:

#!/bin/sh
exec > myroot.out
su - oracle -c scott.sh
exit 0

scott.sh:

Code:

#!/bin/sh
sqlplus -s scott/tiger @ scott.sql
if test $? -ne 0 ; then
   echo 'sqlplus error'
   exit 1
else
   echo 'sqlplus OK'
   exit 0
fi

scott.sql:

Code:

whenever sqlerror exit failure
set termout off
spool scott.lst
select ename from emp;
exit

98_1LE · #3 (**permalink**) 03-22-2002

Thank you very much

inpavan · #4 (**permalink**) 03-23-2002

Jimbo,

I want to know why you are using myroot.sh when executing scott.sh will do the required job. Are you using myroot.sh to change the user or is it useful in other way?

Say, if I want to run the sql query without su, can I just use scott.sh and dont use myrott.sh?

I tested it and if you run scott.sh, then you get the desired result.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
C Shell Script query	bpupdown	Shell Programming and Scripting	1	05-13-2008 07:38 PM
Need Unix script for executing oracle query	ravi gongati	Shell Programming and Scripting	14	03-24-2008 05:53 AM
Script executing sql query	kingluke	Shell Programming and Scripting	2	01-23-2008 01:57 PM
executing a SQL query in shell script	lijju.mathew	Shell Programming and Scripting	1	05-17-2007 11:29 AM
Executing Sql Query Using Shell Script	ragha81	Shell Programming and Scripting	14	07-31-2006 02:15 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by