Hello,

I want to create a generic script that connects to Oracle databases
(from 7.3.4 to 9.2 version) with a guest user. This script should
return 1 if connection is successful and 0 if not.
My problem is due to the 3 requests for password when the connection
fails. I can't find any option that allows to ask for password only
one time.

Has anyone already written a such script?

Thank you in advance.

Fanny

"F. Biguet" <fbiguet (AT) yahoo (DOT) fr> wrote

You know that 7.3.4 is pre-historic and unsupported for years don't you?
I don't know what you exactly like to achieve but this might give you a
hint.
0 = success
1 = failure
Can't remember if "whenever sqlerror exit failure" exists in 7.3.4

== test.sql
myserver{oracle}# cat test.sql
whenever sqlerror exit failure

connect &1/&2@&3
exit
==
== Connect error ===
myserver{oracle}#sqlplus /nolog @test bla bla mydb

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Sep 14 15:38:00 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

myserver{oracle}# echo $?
1
==
== Connect succeeds
myserver{oracle}# sqlplus /nolog @test system manager mydb

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Sep 14 15:38:15 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected.
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
dtodbs1{oracle}# echo $?
0
==

Regards,
Rob

fbiguet (AT) yahoo (DOT) fr (F. Biguet) wrote in message news:<c20c89f0.0409140218.357ed924 (AT) posting (DOT) google.com>...
Hi Fanny,

Connect to SQL*Plus with the nolog option. This doesn't prompt for a
username/password when it starts. You can then use the connect command
to attempt the connection. This doesn't prompt three times if the
connection fails. E.g.

C:\>sqlplus /nolog

SQL> connect guest/password@sid
ERROR:
ORA-12500: TNS:listener failed to start a dedicated server process
SQL>

Put all your connection tests in a script. E.g For windows.
connect_test.sql:
-----------------------------------------------------
set verify off
set head off

host del &4

connect &1/&2@&3
select 'TEST_WORKED' from dual

spool &4
/

spool off

exit
-----------------------------------------------------


Have another scripts that then runs connect_test.sql and parses the
output, E.g.:

test_con.bat
-----------------------------------------------------
sqlplus /nolog @connect_test.sql user password sid log_file
REM Parse the log_file looking for "TEST_WORKED"


There's probably a neater solution, but the use of nolog is the main
point here I think.

Ben

1. You did not say what OS, so let's assume UNIX. Create a script.

sqlplus -L user/password@oracle_sid << END_SQL
whenever sqlerror exit 1
select sysdate from dual;
END_SQL

if [ $? -eq 0 ]
then
echo Successful
else
echo Failed
fi

NOTE: The return code will be 1 only is logon was successful and
select statement fails. Which happens when the database is closed.

2. You will see a failed message everytime a logon fails.

Wario
Oracle DBA

fbiguet (AT) yahoo (DOT) fr (F. Biguet) wrote in message news:<c20c89f0.0409140218.357ed924 (AT) posting (DOT) google.com>...
No need for such a script. Oracle Enterprise Manager deals with this nicely.
and OEM already existed in the stone age.

Sybrand Bakker
Senior Oracle DBA

Thank you very much for all those answers.
I think I will solve my problem soon with all these informations.

I work whit many oracle versions (even oldest one; what client wants,
we do!) on many configuration systems and I must simulate a oracle
connexion from a client PC for a monitoring tool.

Best regards,

Fanny