stringify() or to_string() ?

While I was experimenting with genAI LLMs applied to generating PL/SQL code, I noticed that Claude-Sonnet created a function containing a stringify() method applied to a JSON_ELEMENT_T variable, a method whose existence I wasn't aware of.

l_string := j_elem.stringify();

This method is documented in the serialization methods paragraph in Oracle23ai JSON Developer's Guide, but it turns out that it works also in Oracle19c (at least on version 19.21.0.0.0) even if the guide doesn't mention it.

As far as I could see it's equivalent to method to_string(), so you can pick whichever you like.

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'