Monday, May 19, 2025

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'



No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles