IF condition in connect command in SQL*Plus

oraclesqlplus

I want to fire SQL queries only when the user is connected to SQL*Plus. In my case even if the connection is not there, the rest of the commands are running in my SQL script. 

if "connect &username/&password@&SID"
...

How to proceed with the queries if only the connect statement is successful?

Best Answer

Use whenever sqlerror in your script before connecting.

$ cat 1.sql
whenever sqlerror exit sql.sqlcode
connect bp/bp
select * from dual;

$ cat 2.sql
whenever sqlerror exit sql.sqlcode
connect bp/123
select * from dual;

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 27 10:07:12 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> @1
Connected.

D
-
X

SQL> @2
ERROR:
ORA-01017: invalid username/password; logon denied

$
Related Question