This is not my tip, but Connor Mcdonald‘s. I attended a couple of his sessions at Open World 18 and had to write about this small but really useful feature that he mentioned (thanks Connor).
We are all writing scripts, some are long with lots of commands. When we run these scripts it’s important for us to know if there were any failures. For me, errors are usually critical so I use the “whenever sqlerror” clause of SQL*Plus. With that you can do something like that:
whenever sqlerror exit 1 rollback
If the script encounters any error, SQL*Plus will exit with exit code 1 while rolling back the last transaction (obviously, any committed transactions will not be affected). This works great for me, especially because in most cases I run SQL*Plus from bash so I can check the exit code and continue accordingly.
However, in some cases, you don’t want to fail the session, but you still want to know about errors. This is what errorlogging does:
set errorlogging on
When you set this option, any error encountered by the session will be logged into a table called SPERRORLOG. This table always exists but is not populated. While the errorlogging is turned on, every failed SQL will be logged into this table and a simple query will show what failed, when, by which user and the error message.
Note that this table is not cleared automatically, but the “set errorlogging” has several options to do this and more.
This feature exists since Oracle 11.1 and the full syntax is here (this is the 12.2 doc, but from what I could see it hasn’t changed since 11.1)