JSON_OBJECT_T.GET_CLOB(key) returns the string 'null' if the value is null

Discovery of the day on Oracle 19 version EE 19.21.0.0.0:

JSON_OBJECT_T.GET_CLOB(key) member function returns a CLOB string containing 'null' (lowercase), not a NULL value.

As you can see below the 'null' string is matched by the function NULLIF which replaces it with a real NULL value:

set serveroutput on
declare
  l_procedure clob;
  j_job_desc       json_object_t;
begin
  j_job_desc := json_object_t.parse('{"procedure":null}');
  l_procedure := nullif(j_job_desc.get_clob('procedure'),'null');
  if l_procedure is null then
    dbms_output.put_line('nothing');
  else
    dbms_output.put_line(l_procedure);
  end if;
end;
/

nothing


PL/SQL procedure successfully completed.

The documentation doesn't explain this behavior at all, it just says that the member function will create a CLOB implicitly and that's all,

Ensuring that PL/SQL conditional compilation depending on custom identifiers is based on actual option values

I have a package that in case Oracle APEX is installed, calls a procedure attempting to send a PWA notification and this procedure requires certain APEX objects to exist.

In order to avoid errors at compile time in case Oracle APEX is not installed, I embedded the code within a block that is compiled only if a PLSQL_CCFLAGS identifier returns TRUE and this flag indicates whether or not APEX is installed.

Now, this leads to a potential problem: this flag must be initialized at the session level before compiling the code, and I would like to avoid that the code is not included only because I forgot to set the flag in advance.

I wish there would be some special constants for optional components of the database such as APEX so that I can avoid this type of tricks, but until then I can stick to the following method.

Here the $ERROR directive comes in handy, right after the BEGIN statement of the procedure I put this block of pseudo-code:

BEGIN
  -- ensure conditional compilation is based on proper PLSQL_CCFLAGS configuration
  $IF $$APEX IS NULL $THEN
    $ERROR q'[PLSQL_CCFLAGS named APEX is undefined, 
              execute ALTER SESSION SET PLSQL_CCFLAGS = 'APEX:TRUE|FALSE']'
    $END
  $END

  -- conditional compilation in case APEX is installed
  $IF $$APEX $THEN
  ...
  $ELSE -- APEX not installed
  ... 
  $END
END;

After compiling the code, you can ensure that the code contains the expected logic by calling the following procedure:

set serveroutput on
EXEC DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PROCEDURE','TOOLS','SEND_NOTIFICATION');

In case you forgot to set the flag, here is what you get at compile time:

PLS-00179: $ERROR: PLSQL_CCFLAGS for APEX is undefined,
execute ALTER SESSION SET PLSQL_CCFLAGS = 'APEX:TRUE|FALSE'

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.