Hi,
Oracle 9.2
Solaris 10
From the shell script ........How can we verify whether oracle connection is successful or not ?
Shell script will prompt the user to enter the oracle schema username, password and tns name.
So, how to verify whether oracle connection has been established or not ??
If user entered schema name & password is correct then proceed further else script will again prompt for schema user name & password.
With Regards
#!/bin/ksh
# usage $1 = username $2=pwd, assumes ORACLE_SID and TWO_TASK (if needed) are set
test_conn()
{
usr="$1"
pwd="$2"
sqlplus -s ${usr}/${pwd} << EOF | tr -d '[:space:]'
set feed off
set head off
set pages 0
set verify off
select 'ok' from dual;
exit
EOF
}
oops=1
while [ $oops -eq 1]
do
print 'Enter username: '; read uname
print 'Password: ';
stty -echo
read pass
stty echo
result=$(test_conn $uname $pass)
if [ "$result" = "ok"] then
oops=0
fi
start with that.
Could you pls explain this piece of code. I guess this makes the given input (password) to be invisible..Kindly brief..
stty -echo
read pass
stty echo
See:
man stty
Hi,
Executed the script but getting the below error :
$ ./test_oracle_conn.sh
./test_oracle_conn.sh[18]: select: bad identifier
With Regards
You probably should catch or disable interrupts to the script while reading the password. If the user hits CTRL-C while the script is trying to read the password the TTY will be left in a non-echoing state.
Hi,
On executing the script getting the below error :
$ ./test_oracle_conn.sh
./test_oracle_conn.sh: line 18: syntax error near unexpected token `from'
./test_oracle_conn.sh: line 18: ` select "ok" from dual;'
Sorry, I am new with shell script
With Regards
---------- Post updated at 05:10 PM ---------- Previous update was at 04:31 PM ----------
Hi,
I have done something like this :
printf "$FBOLD\nEnter username: $FREG"
read user_name
printf "$FBOLD\nEnter password: $FREG"
read password
printf "$FBOLD\nEnter TNS name: $FREG"
read tns
sqlplus $user_name/$password@$tns | echo "select 'ok' from dual;" > c:/tmp/check_conn.txt
But,
On checking file check_conn.txt, it contains select 'ok' from dual;
How to pass the output of the select statement in the output file ??
how it can be done ?
With Regards
Your problem is likely to be:
sqlplus << EOF
.
.
.
EOF -- the last one
is the trailer for a here document. It has to be in column #1 (on the left hand side
as far as you can go) in your script. You can use any word that is not a shell keyword or command for a delimiter
Us old guys use << ! .... ! for here doc delimiters
because ! is a single character that is never part of POSIX shell syntax. And we can't type.
Log into an idle instance first, so you can set 'whenever sqlerror ... '
user=<username>
pass=<password>
sqlplus /nolog <<EOF
whenever sqlerror exit 99
connect $user/$pass
whenever sqlerror exit 98 -- change this so only the connect attempt uses '99' as the error code
-- do your normal sql stuff now
exit -- assuming a good login and no other error you log out normally
EOF
echo $? # will be 99 if bad login attempt, 98 if other sql error happened, or 0 if everything was fine.
If possible I would use perl for this, it is perfect for this task.
Search the cpan pages for DBI and DBD::mysql, there are plenty of examples there.
Hi,
I have written the below line in the .sh file
sqlplus $user_name/$password@$tns > c:/tmp/check_conn.txt
In the check_conn.txt file, I can see the redirected output as below :
On executing the script, sqlplus gets open but the script gets hanged, it does not exit from sqlplus.
How to exit from sqlplus so that script can execute furhter ?
With Regards
Try this,
#!/bin/sh
res=`sqlplus $user_name/$password@$tns <<ENDOFSQL
set feed off
set head off
set pages 0
set verify off
select 'ok' from dual;
exit
ENDOFSQL`
if [ "$res" == "ok"]
then
echo "SUCCESS"
else
echo "FAIL"
fi
I would add one more check to the above.
Check below my example:
connStatus=`echo -e "WHENEVER SQLERROR EXIT 1\n SET HEAD OFF\n SET FEEDBACK OFF\n SELECT 'OK' FROM DUAL;\nEXIT" | sqlplus -S -L $user_name/$password@$tns`
sqlPlusRetCode=${?}
if [ "${connStatus}" != "OK" -o ${sqlPlusRetCode} -ne 0 ]
then
echo "ERROR: Database connection not available."
# exit 1
else
echo "INFO: Success."
fi
Maybe you will not need: "echo -e", just "echo".
Regards!
The script ain't hung; it's waiting for you to enter your SQL command!
Think about it - if you type this on the Unix/Linux prompt :
sqlplus $user_name/$password@$tns
you'd start your sqlplus session and the "SQL> " prompt would wait for your next command.
Why would the same thing via a script be any different ?
tyler_durden