Home » Infrastructure » Unix » sqlplus from Unix
sqlplus from Unix [message #179736] Wed, 28 June 2006 09:22 Go to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
Hi gurus,

Need a help. I want to run a sql script from unix shell script.I want to compare two schemas. I want users to enter user name, password and connect string.

example.sh
#!bin/ksh
echo "enter username "
read username
echo "enter password"
read passwd
echo "enter connect string"
read constg
echo "enter master schema"
read mst_schema
echo "enter detail schema"
read dtl_schema
sqlplus -s $username/$passwd@constg <<EOF
@compare_schema.sql
EOF

Now while calling compare_schema.sql I want to pass mst_schema and dtl_schema to it , so that any sql inside the compare_schema.sql should be looked like
select a.name
from table a
where a.owner =('&&mst_schema')
similarly
select b.name
from table b
where b.owner =('&&dtl_schema')

Re: sqlplus from Unix [message #179750 is a reply to message #179736] Wed, 28 June 2006 10:01 Go to previous messageGo to next message
aciolac
Messages: 242
Registered: February 2006
Senior Member
1)Connect by sqlplus with /nolog option
2) in the script compare_schema.sql, until all operations, put the next:
PROMPT specify a username;
DEFINE username = &1
PROMPT specify a password;
DEFINE password = &2
........................
2) After that, put the netx in script:
connect "&&username"/"&&password"--you can set also connect string

After this operatinos, you can execute principal part of your script.
Re: sqlplus from Unix [message #179957 is a reply to message #179750] Thu, 29 June 2006 07:16 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
Calling the sql script works fine. I have changed the script (used DEFINE as suggested).Have done some work around in the shall script. Instead of asking userid, passwd, connecting string seperately, I want user to enter username/passwd@connectstring as a single entry, becuase asking to enter username, password and connect string separetly does not work.
sqlplus -s $username/$passwd@$connect_string gives error, but sqlplus -s $unmpasswdconn does not - any idea why?
Re: sqlplus from Unix [message #179959 is a reply to message #179957] Thu, 29 June 2006 07:20 Go to previous message
aciolac
Messages: 242
Registered: February 2006
Senior Member
use connect "&&username"/"&&password"@"&&connect_string"
Symbol $ are interpreted by Unix as variable and can give errors.
Previous Topic: Not able to include wc -l inside expr
Next Topic: Unix file comparison
Goto Forum:
  


Current Time: Mon Nov 30 11:55:38 CST 2020