8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Conditional Compilation in Oracle 10g Database Release 2
Conditional compilation allows PL/SQL code to be tailored to specific environments by selectively altering the source code based on compiler directives. It is considered a new feature of Oracle 10g Release 2, but is available in Oracle 10g Release 1 (10.1.0.4.0).
Compiler flags are identified by the "$$" prefix, while conditional control is provided by the $IF-$THEN-$ELSE syntax.
$IF boolean_static_expression $THEN text [ $ELSIF boolean_static_expression $THEN text ] [ $ELSE text ] $END
As an example, let's assume that all application debugging is performed by calling a procedure called DEBUG
.
Conditional compilation can be used to provide an on/off switch for the debug, as well as influencing the debug information that is produced. The following procedure implements a variety of debug behavior using conditional compilation.
CREATE OR REPLACE PROCEDURE debug (p_text IN VARCHAR2) AS $IF $$debug_on $THEN l_text VARCHAR2(32767); $END BEGIN $IF $$debug_on $THEN $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN l_text := SUBSTR(p_text, 1 ,233); $ELSE l_text := p_text; $END $IF $$show_date $THEN DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text); $ELSE DBMS_OUTPUT.put_line(p_text); $END $ELSE NULL; $END END debug; /
The debug_on
flag acts as an on/off switch, as a value of FALSE will result in an empty procedure. Assuming debug is enabled, the DBMS_DB_VERSION
package is used to determine if the input text should be truncated to prevent errors in DBMS_OUTPUT
. If the code is running on a Release 2 server this truncation is not necessary due to the enhancements in the DBMS_OUTPUT
package. The show_date
flag is used to determine if a date prefix should be added to the debug message.
Once the procedure is compiled the complete source is stored in the database, including the conditional code directives.
SET PAGESIZE 30 SELECT text FROM user_source WHERE name = 'DEBUG' AND type = 'PROCEDURE'; TEXT ---------------------------------------------------------------------------------------------------- PROCEDURE debug (p_text IN VARCHAR2) AS $IF $$debug_on $THEN l_text VARCHAR2(32767); $END BEGIN $IF $$debug_on $THEN $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN l_text := SUBSTR(p_text, 1 ,233); $ELSE l_text := p_text; $END $IF $$show_date $THEN DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text); $ELSE DBMS_OUTPUT.put_line(p_text); $END $ELSE NULL; $END END debug; 21 rows selected. SQL>
The PRINT_POST_PROCESSED_SOURCE
procedure of the DBMS_PREPROCESSOR
package displays the post-processed source.
SET SERVEROUTPUT ON SIZE UNLIMITED BEGIN DBMS_PREPROCESSOR.print_post_processed_source ( object_type => 'PROCEDURE', schema_name => 'TEST', object_name => 'DEBUG'); END; / PROCEDURE debug (p_text IN VARCHAR2) AS BEGIN NULL; END debug; PL/SQL procedure successfully completed. SQL>
As expected, the lack of compile flags results in an empty debug procedure. Setting the debug_on
and show_date
compiler flags results in debug messages printed with a date prefix.
ALTER PROCEDURE debug COMPILE PLSQL_CCFLAGS = 'debug_on:TRUE, show_date:TRUE' REUSE SETTINGS; BEGIN DBMS_PREPROCESSOR.print_post_processed_source ( object_type => 'PROCEDURE', schema_name => 'TEST', object_name => 'DEBUG'); END; / PROCEDURE debug (p_text IN VARCHAR2) AS l_text VARCHAR2(32767); BEGIN l_text := p_text; DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text); END debug; PL/SQL procedure successfully completed. SQL>
Switching off the show_date
compiler flag results in debug messages printed without a date prefix.
ALTER PROCEDURE debug COMPILE PLSQL_CCFLAGS = 'debug_on:TRUE, show_date:FALSE' REUSE SETTINGS; SET SERVEROUTPUT ON SIZE UNLIMITED BEGIN DBMS_PREPROCESSOR.print_post_processed_source ( object_type => 'PROCEDURE', schema_name => 'TEST', object_name => 'DEBUG'); END; / PROCEDURE debug (p_text IN VARCHAR2) AS l_text VARCHAR2(32767); BEGIN l_text := p_text; DBMS_OUTPUT.put_line(l_text); END debug; PL/SQL procedure successfully completed. SQL>
The result of conditional compilation is the removal of unnecessary code. PL/SQL is an interpreted language, so removing unnecessary code may produce performance improvements in some circumstances.
For more information see:
Hope this helps. Regards Tim...