OraFAQ Forum: SQL & PL/SQL » Using WHENEVER ERROR in login.sql

Home » SQL & PL/SQL » SQL & PL/SQL » Using WHENEVER ERROR in login.sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using WHENEVER ERROR in login.sql [message #264110]

Fri, 31 August 2007 17:33

scottmccoll
Messages: 2
Registered: August 2007

Junior Member

Hello out there, I am hoping someone can help me find an answer to my problem.

I want to create the situation where when I or one of my users runs sqlplus (on Solaris) and encounters an error (SQL or OS) that sqlplus will exit - and not simply hang in the background waiting for input that is not coming. We usually run our sql scripts with some kind of wrapper script to catch the errors and report it back to the user. So when I do something like "sqlplus user/password @script" and that has an error, the process just waits in the background.

I have seen that I can use
WHENEVER SQLERROR EXIT 5 ROLLBACK;
WHENEVER OSERROR EXIT 10 ROLLBACK;
to get my sqlplus session to exit from the command line.

So I figured that I could put my WHENEVER statements in the login.sql file, that way I could always has that functionality enabled and not have to alter all my scripts.

This does not seem to work though when I try to run a script from the command line as "sqlplus user/password @script". But it does seem that my WHENEVER settings are there, because when I do just the "@ script" in the sqlplus session, I get the error I expected and it closes the session.

I have include an example of what I am seeing below. /tmp/xxx.sql is a bogus file, so I should get the file not found message and have sqlplus exit.

mccoll@rfautopub[45]> cat login.sql
WHENEVER SQLERROR EXIT 5 ROLLBACK;
WHENEVER OSERROR EXIT 10 ROLLBACK;

undefine usr db
col usr new_value usr
col db new_value db

set termout off
select lower(user) usr,
substr(global_name, 1, instr(global_name, '.')-1) db
from global_name
/

set termout on

set sqlprompt '&&usr.@&&db.> '

mccoll@rfautopub[46]> sqlplus hino_pub/hino_pub @ /tmp/xxx.sql

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Aug 31 18:27:52 2007

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SP2-0310: unable to open file "/tmp/xxx.sql"

[Note: sqlplus does not exit ]

hino_pub@PUBDEV>
hino_pub@PUBDEV>

hino_pub@PUBDEV> WHENEVER
WHENEVER SQLERROR EXIT 5 ROLLBACK
WHENEVER OSERROR EXIT 10 ROLLBACK

[Note: WHENEVER options appear to have been set ]

hino_pub@PUBDEV> @ /tmp/xxx.sql
O/S Message: No such file or directory

[ Note: now it exits ]

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

mccoll@rfautopub[47]> echo $?
10

Thanks for you help.

Scott McColl

Report message to a moderator

Re: Using WHENEVER ERROR in login.sql [message #264139 is a reply to message #264110]

Sat, 01 September 2007 01:14

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

When you execute "@/tmp/xxx.sql" at SQL*Plus prompt, SQL*Plus has already executed your login.sql and then execute your command, see the file is not there and follows the "whenever" clause.
When you indicate "@/tmp/xxx.sql" on command line, SQL*Plus checks BEFORE executing login.sql.

Code: [Select all] [Show/ hide]

C:\>type login.sql
prompt LOGIN.SQL

C:\>sqlplus -s michel/michel
LOGIN.SQL
@t2.sql
SP2-0310: unable to open file "t2.sql"
exit

C:\>sqlplus -s michel/michel @t2
SP2-0310: unable to open file "t2.sql"
LOGIN.SQL
exit

As you can see, in the first case the prompt LOGIN.SQL is BEFORE call to the script, in the latter one it is AFTER.

Regards
Michel

Report message to a moderator

Re: Using WHENEVER ERROR in login.sql [message #264752 is a reply to message #264110]

Tue, 04 September 2007 08:03

scottmccoll
Messages: 2
Registered: August 2007

Junior Member

So my issue comes from this:

"When SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed.

Thereafter, sqlplus will try to find a local login script called login.sql in the directory where you start sqlplus from, alternatively the directories listed in the SQLPATH environment variable. When found, sqlplus will execute it."

To me, that says that SQL*Plus will execute that login.sql file if it finds it, before it does anything else. This does not appear to be the case when I try to run another SQL script as a command line parameter to sqlplus. That is a fundamental difference between running a script as a command line parameter and running the script at the first SQL*plus prompt. I would expect them to run identically.

Why is the behavior of the login.sql different in these 2 cases?
Does this behavior difference extend to the global login file (glogin.sql)?

Thanks - Scott McColl

Report message to a moderator

Re: Using WHENEVER ERROR in login.sql [message #264757 is a reply to message #264752]

Tue, 04 September 2007 08:20

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Why is the behavior of the login.sql different in these 2 cases?

You should ask Oracle for that. No visible reason. I agree it is quite annoying.

Quote:

Does this behavior difference extend to the global login file (glogin.sql)?

Yes, just execute the same test if you want to verify.

Regards
Michel

Report message to a moderator

Previous Topic:	tree base query help
Next Topic:	to compare dates in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 20 13:08:01 CDT 2025