SQLcl: MCP Server & SQL Prompt

Tested in SQLcl 23.1

(But also happens in prior versions, e.g. 22.4, or in SQL Dev 21.4.3…)

TL/DR: the SHOW ERRORS command should report whatever errors and warnings are present in the ALL_ERRORS view for the concerned program unit. It turns out, in SQLcl that command is influenced by the plsql_warnings parameter, possibly causing all warnings to be missing in the readout.

SQL> alter session set plsql_warnings = "ENABLE:ALL, DISABLE:6009";

Session altered.

SQL> show parameter plsql_warnings
NAME           TYPE   VALUE
-------------- ------ --------------------------
plsql_warnings string ENABLE:ALL, DISABLE:  6009

SQL> create or replace function f_silly_tst return varchar2
  2  is
  3     l_obj_name varchar2(10);
  4     l_obj_id number;
  5  begin
  6     l_obj_id := 0;
  7     select o.object_name into l_obj_name
  8       from all_objects o
  9      where o.object_name >= l_obj_id
 10      fetch first 1 row only;
 11     return l_obj_name;
 12  end f_silly_tst;
 13* /

Function F_SILLY_TST compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
1/1       PLW-05018: unit F_SILLY_TST omitted optional AUTHID clause; default value DEFINER used
9/11      PLW-07204: conversion away from column type may result in sub-optimal query plan

So far, everything is fine—except my code, of course! SQLcl has shown errors and warnings automatically, as part of the feedback of the create function statement; SQL*Plus does not do this.

Now, what if we use the SHOW ERRORS command? Many compilation scripts originally written for SQL*Plus always call that command, because otherwise SQL*Plus provides with a very terse feedback for such statements. So let's try.

Please note that (as discussed in this thread) SHOW ERRORS may turn out to be painfully slow in SQLcl, and then… surprise!

SQL> show errors

No errors.

Where did my warnings go?

… Long story short: SQLcl has ignored them, due to the value of the plsql_warnings parameter! Changing it back to “ENABLE:ALL” is enough to see all warnings again.

SQL> alter session set plsql_warnings = "ENABLE:ALL";

Session altered.

SQL> show errors

Errors for FUNCTION PDB_ADMIN.F_SILLY_TST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit F_SILLY_TST omitted optional AUTHID clause; default value DEFINER used
9/11     PLW-07204: conversion away from column type may result in sub-optimal query plan

Tracing the session confirms that the implementation of the SHOW ERRORS command in SQLcl does the following (among other queries):

1/ Retrieve the value of the plsql_warnings parameter:

SELECT value 
FROM
 v$parameter WHERE name = 'plsql_warnings'

And:

2/ With the tested value of plsql_warnings="ENABLE:ALL, DISABLE:6009", use the following query in order to retrieve the errors:

select substr(line||'/'||position,0,10) linenum ,text 
from
 all_errors  where  type = :TYPE  and    name = :OBJECT_NAME  and    owner = 
  :OWNER and    attribute != 'WARNING' order by line asc, position, sequence 
  desc

Conclusion: it looks like there are 2 mistakes here:

1/ SHOW ERRORS should completely ignore the plsql_warnings parameter

The plsql_warnings parameter is compile-time directive: it is a per-object compilation setting which governs which PL/SQL warnings will be recorded or silenced by the PL/SQL compiler during compilation (or recompilation, if reusing prior settings). It should never be interpreted by the client as a demand to not report existing warnings that have already been recorded in the ALL_ERRORS view.

And:

2/ The value of the parameter is erroneously handled, causing all warnings to be silenced if any one is disabled.

But please, by all means, the one issue to be fixed here is the first one.

For the sake of comparison, here's the single query run by SQL*Plus (tested version: 23c) as part of the SHOW ERRORS command:

SELECT TO_CHAR(LINE)||'/'||TO_CHAR(POSITION) "LINE/COL", TEXT "ERROR" 
FROM
 ALL_ERRORS A WHERE A.NAME = UPPER(:NMBIND_SHOW_OBJ) AND A.TYPE = 'FUNCTION' 
  AND A.OWNER = (SELECT (SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')) FROM 
  SYS.DUAL) ORDER BY LINE, POSITION, ATTRIBUTE, MESSAGE_NUMBER

And please note that SQL*Plus is smart enough, if a function name in mixed case is being used (e.g. "FSillyTst"), to change that query into:

SELECT TO_CHAR(LINE)||'/'||TO_CHAR(POSITION) "LINE/COL", TEXT "ERROR"
FROM
 ALL_ERRORS A WHERE A.NAME = :NMBIND_SHOW_OBJ AND A.TYPE = 'FUNCTION'
  AND A.OWNER = (SELECT (SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')) FROM
  SYS.DUAL) ORDER BY LINE, POSITION, ATTRIBUTE, MESSAGE_NUMBER

I fail to see why SHOW ERRORS in SQLcl should behave much differently.

Regards,