Friday, April 23, 2010

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:
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.
POST A COMMENT

17 Comments:

Blogger anonymoustravis said....

This is a nifty feature. Thanks for the writeup.

Fri Apr 23, 04:39:00 PM EDT  

Anonymous Doug Burns said....

Nice one - I had no idea. That could come in really useful in the tons of database scripts our release process runs.

Sat Apr 24, 05:21:00 AM EDT  

Anonymous Anonymous said....

This is cool. Excellent

Sun Apr 25, 06:25:00 AM EDT  

Blogger Connor McDonald said....

Presented this in Queensland in 2008 with you sitting in the audience ! Shucks, talk about not listening !!!

:-)

Mon Apr 26, 01:53:00 AM EDT  

Blogger Thomas Kyte said....

@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 :)

Mon Apr 26, 06:49:00 AM EDT  

Blogger david.pitt said....

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.

Mon Apr 26, 08:11:00 PM EDT  

Anonymous Anonymous said....

- 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)

Thu Apr 29, 11:37:00 AM EDT  

Blogger David Mann said....

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!

Tue May 04, 01:34:00 PM EDT  

Blogger david.pitt said....

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?

Tue May 04, 07:58:00 PM EDT  

Blogger Andre said....

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;

Wed May 05, 11:03:00 AM EDT  

Blogger cse said....

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

Tue May 18, 12:27:00 PM EDT  

Blogger david.pitt said....

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?

Tue May 18, 07:32:00 PM EDT  

Anonymous Anonymous said....

sperrorlog table is not available in ASM instance when connected as "/ as sysasm".

Tue Aug 17, 09:00:00 AM EDT  

Blogger Sean said....

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.

Thu Feb 10, 11:31:00 AM EST  

Blogger Sean said....

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)

Thu Feb 10, 04:00:00 PM EST  

Anonymous Anonymous said....

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

Wed Oct 17, 07:32:00 AM EDT  

Blogger Thomas Kyte said....

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.

Wed Oct 17, 07:38:00 AM EDT  

POST A COMMENT

<< Home