DBMS_STATS.GATHER_TABLE_STATS fails with ORA-01760 illegal argument for function

This is just a reminder in case I'll stumble upon this problem again in the future.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

I was trying to execute something like this:

begin
 DBMS_STATS.GATHER_TABLE_STATS(
  ownname => ...,
  tabname => ...,
  cascade => TRUE,
  options => 'GATHER AUTO'
 );
end;

ORA-01760: illegal argument for function

As described in this excellent post-mortem analysis made by Jonathan Lewis, I believe there is a combination of factors that causes the problem, certainly I do have a materialized view referencing the table being analyzed, but may be the cascade or the specific options also play a role in this bug that seems to span 20+ years of database versions without a definitive solution.

Jonathan also mentions a workaround fixing the problem, which worked at least in my case, that is issuing:

ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;

See message translations for ORA-01760.

Errors returned by expressions in SQL queries are not necessarily the same as the errors returned by equivalent PL/SQL expressions.

Have you ever noticed that error codes change depending on whether the context is SQL or PL/SQL?

DECLARE
    x number := 0;
    y number;
BEGIN
    select log(10,x)
      into y
      from dual;
END;
/

The PL/SQL block above returns following error:

ORA-01428: argument '0' is out of range
ORA-06512: at line 5

But if I change the way I assign the value to y, the error will be much more generic.

DECLARE
    x number := 0;
    y number;
BEGIN
    y := log(10,x);
END;
/

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5 

Now, this is a trivial case, but imagine a situation where you initially wrote the code in a certain way and then it turns out you have to change completely the approach for some reason, a business request, code refactoring, whatever.
If there is an EXCEPTION block catching a specific error, ORA-01428 for instance, after the change it won't catch that error any longer, presumably with some consequences for the final outcome of the procedure or function.

Enhanced simple CASE statement syntax in Oracle 23ai

Oracle 23ai extends the syntax of the so-called "simple" CASE statement in PL/SQL, that is the version of CASE where an expression appears right after the CASE keyword.
The list of WHEN blocks now allows dangling expressions (in red below), that is conditional expressions where the left operand is missing.

DECLARE
    x number;
BEGIN
    case x
    when is null THEN
      dbms_output.put_line('null');
    when 1 then 
        dbms_output.put_line('1');
        return;
    when > 1 then 
        dbms_output.put_line('greater than 1');
        return;
    else 
        dbms_output.put_line('else');
        return;
    end case;
END;
/

The PL/SQL block above would raise PL/SQL parsing errors in earlier versions (PLS-00103 on lines 5 and 10).

It is worth noting that in earlier versions the only way to catch WHEN x IS NULL, would be to replace NULL with some other value or, better, use the "searched" CASE statement instead.

For more information, please refer to the official Oracle 23ai documentation.