sqlcl errorlog configuration

Question

I like to setup my oracle database scripts to fail in the event of any database or OS problem.

I've been considering migrating to SQLCL, but the SQL*Plus approach I'd taken for detecting and triggering rollback for non-database problems is failing with SQLCL and I wondered if anyone has an alternative solution (or a good workaround).

I've been using SET ERRORLOGGING to direct any SP- errors to SPERRORLOG, where I can detect them before committing and decide rollback/branch/continue/alert, etc.

I grabbed the latest sqlcl (June 2016 sqlcl-4.2.0.16.175.1027), hoping for a fix to the following problem, but it persists.

When issuing SET ERRORLOGGING ON, I get the following:

SQL> SET ERRORLOGGING ON 
SP2-0158: unknown SET option beginning "errorloggi..."

The manual entry seems to indicate nothing else for this option has changed in SQLCL.

SQL> help set errorlogging 
SET ERRORLOGGING 
ERRORL[OGGING]{ON|OFF} 
[TABLE [schema.]tablename]     [TRUNCATE] [IDENTIFIER  identifier]

Is this just a bug, or is there something else needed to get ERRORLOGGING up and running?

If this is a bug, is there a good alternative to detect SP(2)- errors?

What version of Oracle you are running? SQL*PLUS error logging feature was introduced in version 11.1 — Nick Krasnov, Jul 31 '16 at 01:37
Thanks @NicholasKrasnov I'm on 11GR2. SPERRORLOG works fine in SQLPlus, only SQLCL fails. — alexgibbs, Jul 31 '16 at 01:40

score 1 · Accepted Answer · answered Jul 31 '16 at 02:07

1

Well, it seems, despite the fact that the errorlogging setting is present among available SQLcl's set settings, it's unsupported at the moment.

Run show <setting> command to check if a setting's supported or not.

./sql -v

SQLcl: Release 4.2.0.16.175.1027 RC

SQL> help set errorlogging

SET ERRORLOGGING
   ERRORL[OGGING] {ON|OFF}
   [TABLE [schema.]tablename] 
   [TRUNCATE] [IDENTIFIER identifier]

SQL> show errorlogging
errorlogging Unsupported

answered Jul 31 '16 at 02:07

Nick Krasnov

26,886
6
61
78

Ok that explains it. Thanks Nicholas, I'll accept. I'm admittedly surprised that this information isn't included directly in Help though. – alexgibbs Jul 31 '16 at 03:57

score 1 · Answer 2 · answered Aug 29 '17 at 12:02

We got there. Its supported now and on the latest OTN release or the Oracle Database release.

SQL> conn barry/oracle@localhost:1521/xe
Connected.
SQL> show errorlogging
errorlogging is OFF
SQL> set errorlogging on
SQL> show errorlogging
errorlogging is ON TABLE SPERRORLOG
SQL> select * from NOTATABLE;

Error starting at line : 1 in command -
select * from NOTATABLE
Error at Command Line : 1 Column : 15
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:


SQL> set sqlformat ansiconsole
SQL> /

USERNAME  TIMESTAMP                     SCRIPT  IDENTIFIER  MESSAGE                                   
STATEMENT

BARRY     29-AUG-17 12.55.45.000000000                      ORA-00942: table 
or view does not exist
select * from NOTATABLE

Great to hear, thanks Barry. This feature will help expand the situations where sqlcl can be used. — alexgibbs, Aug 29 '17 at 12:14

sqlcl errorlog configuration

2 Answers2