Why I Like SQL*Plus for Deployment Scripts

SQLcl is eating my error messages

Posted by Ottmar Gobrecht on , tagged with Oracle, Script and SQL*Plus

The following simplified example deployment masterscript is given:

set define off verify off feedback off
whenever sqlerror exit sql.sqlcode rollback
spool deployment.log

prompt
prompt Example Deployment Master Script
prompt ========================================

prompt Step 1: Dummy for demonstration
-- normally we call here some other scripts like
-- @other-script.sql

prompt Step 2: Error test in anonymous block
begin
  raise_application_error(-20000, 'Error test for SQL*Plus <> SQLcl comparison');
end;
/

prompt ========================================
prompt Deployment Done :-)
prompt

spool off

When we call this in SQL*Plus (version 19.5), we get the following output (in the console and also in deployment.log) with an indication of what went wrong with our deployment:

Example Deployment Master Script
========================================
Step 1: Dummy for demonstration
Step 2: Error test in anonymous block
begin
*
ERROR at line 1:
ORA-20000: Error test for SQL*Plus <> SQLcl comparison 
ORA-06512: at line 2 

Ok, this error message is stupid, but usually I get some useful information here to fix my problem in the deployment script - especially when the problematic script is a bit longer…

When we call this in SQLcl (latest version 19.4 on Windows) we get the following output (in the console and also the deployment.log):

Example Deployment Master Script
========================================
Step 1: Dummy for demonstration
Step 2: Error test in anonymous block

Mhhh… What was going on here? Why was my script terminating in step 2?

That's one reason why I like SQL*Plus a bit more than SQLcl for deployment scripts. The fast start time of SQL*Plus is another reason. Nevertheless SQLcl is a cool command line tool.

Happy scripting :-)
Ottmar

P.S.: There is a small Twitter discussion around this…

UPDATE January 3, 2020

Jeff Smith has confirmed that this bug was fixed in SQLcl version 19.4. I was using 19.4 - but on Windows. It turns out that the bug is still there - only on Windows, not Mac, not Linux…

So, if you are facing this problem on Windows then you have to wait for a future release or set as a workaround the feedback on:

set define off verify off feedback on
<snip>

Here the output on my Windows 10 laptop:

Demo Deployment Master Script
========================================
Step 1: Dummy for demonstration
Step 2: Begin error test in anonymous block
Rollback

Error starting at line : 10 File @ C:\temp\deployment.sql
In Command -
begin
  raise_application_error(-20000, 'error test for SQL*Plus <> SQLcl comparison');
end;
Error report -
ORA-20000: error test for SQL*Plus <> SQLcl comparison
ORA-06512: at line 2
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

If you are on Mac OS or Linux than you have to update to the latest SQLcl version 19.4 or use also the described workaround.

Again: happy scripting :-)
Ottmar