0

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?

1

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

$
| improve this answer | |
0

Start your commands with the following:

WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
  select * from dual;
end;
/

When you are not connected then the SQL will fail and the other SQL statements will not get executed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.