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.

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.