Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

HP-UX script hangs when SQLPLUS login to Oracle fails

Wim Korten
Advisor

HP-UX script hangs when SQLPLUS login to Oracle fails

Hi all,

In my unix scripts I use SQL*Plus to do some activities in Oracle. If the login in Oracle fails (wrong password, instance not available, ...) SQL*Plus waits for you to enter a valid login. In batch this means that the unix-script waits indefinite.

System HP-UX 11.0 and Oracle 8.1.7.4

Statement:
sqlplus -S user/wrongpasswd@instance @some.sql

I would like SQL*Plus to exit after a wrong login with a returncode<>0. As the SQL*Plus manual states in Chapter 6.

Who knows a solution.

THX,
Wim
Limited by Technology
5 REPLIES
Mark Grant
Honored Contributor

Re: HP-UX script hangs when SQLPLUS login to Oracle fails

You could try something like this just before your sql command

"sleep 10 && kill $$ &"

And then just after your sql command

"kill $!"

I'm sure there's a neater way but this is more fun!
Never preceed any demonstration with anything more predictive than "watch this"
Stan_17
Valued Contributor

Re: HP-UX script hangs when SQLPLUS login to Oracle fails

Hi Wim,

There is no straight way to do this unless the database is 9.2, where sqlplus -L does exactly what you asked for.

In 8.1.7.x, perhaps you can first connect to sqlplus like sqlplus /nolog (this doesn't require any username or password), then from idle prompt try connecting to the database with username/password@connectstring. If that fails, exit which is easy to trap.

-
hth,
-Stan
Graham Cameron_1
Honored Contributor

Re: HP-UX script hangs when SQLPLUS login to Oracle fails

As pointed out by Stan, you can use the /nolog option, (but this is not limited to 817, any version will work).

Combine this with whenever sqlerror to achieve your desired effect.

Here is a fragment from my session:

--
# sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 24 08:38:04 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> whenever sqlerror exit 99
SQL> conn ice/xxx
ERROR:
ORA-01017: invalid username/password; logon denied


sedling:/home/u263478> echo $?
99
--

And by the way, sqlplus user/pass on the same line is very insecure, because anyone running "ps -ef|grep sqlplus" will see the full command line including the password.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Ekkehard Schulz
Occasional Advisor

Re: HP-UX script hangs when SQLPLUS login to Oracle fails

Hello Wim, let sqlplus read from stdin. Try this

#!/bin/sh

cat </tmp/1.sql
select * from cat;
exit
EOF

echo With correct pwd

sqlplus -S <guest/guest
@/tmp/1.sql
EOF

echo Error:$?

echo With wrong pwd

sqlplus -S <guest/guest1
@/tmp/1.sql
EOF

echo Error:$?

It doesn't hang, because the lines in 1.sql are used for the 2 and 3 login if a wrong pwd was given.
Wim Korten
Advisor

Re: HP-UX script hangs when SQLPLUS login to Oracle fails

THX
Limited by Technology