HP-UX script hangs when SQLPLUS login to Oracle fails

[Wim Korten]

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

[Mark Grant]

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!

[Stan_17]

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]

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

[Ekkehard Schulz]

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]

THX