A new thing about sql*plus
What I learned today was - a new feature in the venerable old tool SQL*Plus.
I was asked about trapping the SP2 errors in SQL*Plus, those you get when you have a bad SQL*Plus command - like this:
In that case - neither of the SQL*Plus error handling bits:
will work for you - it is not an OS error like "unable to open spool file", it is not a SQL error - selct is not SQL, it never got to the SQL layer. that SP2 error is uncatchable. And I wrote as much.
Things change - my answer is dead on correct, for version 10gR2 and before (hey, the question was from someone using 10gR2 - so technically, I was correct ;) ). It is not technically true in 11g and above.
SQL*Plus in 11g added an "error logging" facility. A session may issue:
and have any SQL, OS or SP2 errors logged into a logging table, similar to DML error logging. Additionally - you can have your errors tagged with an identifier, making it easy to find your error records. So, you can now check (using SQL) at various points in time to see if you've hit an error - or your program that runs sqlplus and runs a script can check to see if any errors occurred in your session easily.
thanks to Enrique Aviles for pointing it out and thanks to Arup Nanda for writing it up (ctl-f for SQL*Plus Error Logging on that page).
Note that you need the 11g SQL*Plus, not just an 11g database with an old sqlplus connected to it! This is a feature of SQL*Plus.
On the flipside though, this means it is available for older database releases! You can connect to 9i with 11g SQL*plus and use this:
I was asked about trapping the SP2 errors in SQL*Plus, those you get when you have a bad SQL*Plus command - like this:
ops$tkyte%ORA11GR2> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
In that case - neither of the SQL*Plus error handling bits:
OSERROR
SQLERROR
will work for you - it is not an OS error like "unable to open spool file", it is not a SQL error - selct is not SQL, it never got to the SQL layer. that SP2 error is uncatchable. And I wrote as much.
Things change - my answer is dead on correct, for version 10gR2 and before (hey, the question was from someone using 10gR2 - so technically, I was correct ;) ). It is not technically true in 11g and above.
SQL*Plus in 11g added an "error logging" facility. A session may issue:
SQL> set errorlogging on
and have any SQL, OS or SP2 errors logged into a logging table, similar to DML error logging. Additionally - you can have your errors tagged with an identifier, making it easy to find your error records. So, you can now check (using SQL) at various points in time to see if you've hit an error - or your program that runs sqlplus and runs a script can check to see if any errors occurred in your session easily.
thanks to Enrique Aviles for pointing it out and thanks to Arup Nanda for writing it up (ctl-f for SQL*Plus Error Logging on that page).
Note that you need the 11g SQL*Plus, not just an 11g database with an old sqlplus connected to it! This is a feature of SQL*Plus.
On the flipside though, this means it is available for older database releases! You can connect to 9i with 11g SQL*plus and use this:
[tkyte@dellpe ~]$ sqlplus scott/tiger@ora9ir2
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 23 15:36:51 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
scott%ORA9IR2> set errorlogging on
scott%ORA9IR2> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
scott%ORA9IR2> select timestamp, username, script, statement, message
2 from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
-------------------------------------------------------------------------------
SCRIPT
-------------------------------------------------------------------------------
STATEMENT
-------------------------------------------------------------------------------
MESSAGE
-------------------------------------------------------------------------------
23-APR-10 03.37.02.000000 PM
SCOTT
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
posted by Thomas Kyte at 3:24 PM
17 Comments:
This is a nifty feature. Thanks for the writeup.
Nice one - I had no idea. That could come in really useful in the tons of database scripts our release process runs.
This is cool. Excellent
Presented this in Queensland in 2008 with you sitting in the audience ! Shucks, talk about not listening !!!
:-)
@connor,
that would be at least the 2nd time I've learned something new - again - from you :)
I remember sitting in a session at Hotsos - you were doing some new 9i features that you probably haven't heard about....
You started talking for "for update wait N" - the ability to wait N seconds for a lock. I wrote that down thinking "that is cool, have to remember that..." and then you pointed me out in the audience and said you had to give me credit for telling you about it...
I had forgotten I knew it :)
So, I guess in addition to unlearning things, learning truly new things - there is also the category of relearning forgotten things.
Besides, I was so whacked out timezone wise in Queensland :)
Tom thanks for the write-up I am the original poster.
Contrary to earlier comments, this 'feature' is not nifty, nice, cool or excellent. It is a poor workaround for the inability to trap SP2 errors in sqlplus via a whenever clause.
- tested with set "errorlogging on" ...
... inside shell script that already has "whenever sqlerror exit" (setup to exit with sql return code 174)
- this addition makes it exit with sql return code of 0 (instead of 174)
Nice to know. The only way I was able to catch those errors before was by spooling and grepping the output for error text. I always had to have a step after the SQLPLUS execution in order to look for errors - not very elegant!
David, having to grep for errors sucks big-time. This just confirms how unsuitable sqlplus is for serious scripting work.
Hasn't anyone written a replacement for sqlplus, in the same way that sqsh was written as a replacement for the equally poor Sybase isql utility?
I have to agree with others that considers this a poor workaround.
What makes this feature useless for me is that updates to the sperrorlog table is done within the same transaction as the sqlplus scope itself. So if a script fails, and executes a rollback before it finishes, the log will not reflect the new log entry!
set errorlog on
selct * from dual;
select count(*) from sperrorlog;
rollback;
select count(*) from sperrorlog;
Hi,
I've decided to use this feature calling sqlplus from Oracle Data Integrator, so I've done the code below :
----------------------
set errorlogging on identifier s1
delete from sperrorlog where identifier = 's1';
commit;
spool <%=odiRef.getSchemaName()%>/<%=baseNomFichier%>.data
spool off
col nombreErreursSP new_value nombreErreursSP
select count(*) as nombreErreursSP from sperrorlog where identifier = 's1';
WHENEVER OSERROR EXIT 2 ROLLBACK;
WHENEVER SQLERROR EXIT 3 ROLLBACK;
EXIT nombreErreursSP
---------------------
It works well but if the sperrorlog table doesn't exist yet, it doesn't create it because using the "identifier s1" clause. Using simply "set errorlogging on" creates the sperrorlog table.
So I did this :
set errorlogging on
set errorlogging on identifier s1
And it works, it's a kind of workaround for the ...hu... workaround ;o)
Regards,
christophe
Hi Christophe,
I had to read this 3 times:
"I've decided to use this feature calling sqlplus from Oracle Data Integrator"
I don't know anything about Oracle Data Integrator, but it would seem to be totally misnamed if it can't talk to an Oracle database. What on earth is going on?
sperrorlog table is not available in ASM instance when connected as "/ as sysasm".
Does anyone know how to capture the script name when launching sqlplus from a batch file?
sqlplus un/pw@dbname @script1.sql
I have edited my glogin.sql to set errorlogging on
if there is an error in @script1.sql, the error is logged to sperrorlog table, just not the script name.
This feature is great, just can't find any documentation on above. Thanks for a great blog Tom.
Support wasn't too helpful. But a silly workaround is to call script1 from script2, and any errors in script1 will have the script1 name referenced in sperrorlog table.
1. Edit your glogin.sql script so that
set errorlogging on
is in the glogin.sql file (this ensures that every session of SQL has errorlogging and will create then append errors to the sperrorlog table)
2. create a script called silly1.sql
select count(*) from dual;
select count(*) from fred;
3. create a batch file called sillybatch.bat
sqlplus username/password@yourdatabase @silly1.sql
4. Open command prompt and navigate to whereever your sql script and batch file are located, then type
sillybatch
5. Open up sqlplus and type select * from sperrorlog.... there will be no reference to silly1.sql in the script column...
6. create script silly2.sql
@silly1.sql
7. edit sillybatch.bat to read
sqlplus username/password@yourdatabase @silly2.sql
8. repeat step4
now the error in silly1.sql is logged AND references silly1.sql in the script column.
This may be useful to those running hundreds of scripts using batch files.
I've asked support to see if they can enhance errorlogging to capture script names from batch files (assuming set errorlogging on is set in glogin.sql)
hi
I have tried to use errorlogging to capture sql errors. But if the sql statement is too big (has 30 lines)
then error logging is not working.
Please tell me to capture the big sql statements also
that looks like a definite bug in sqlplus - they are not using binds and build an insert with literals.
I'll file a bug against that.
POST A COMMENT
<< Home