Streamline Your Oracle Production Code with Conditional Compilation

Streamline Your Oracle Production Code with Conditional Compilation

f you’re an Oracle PL/SQL developer, you have two familiar needs:

  1. Include as much debugging code as possible without risking a performance hit when the code is running in production
  2. Create code that takes advantage of the newest features in the latest Oracle Database versions but still runs on older versions?without creating “bloatware” that stuffs the shared pool with separate code for each database version

Developers working in other languages have long had an easy solution that addresses both these needs: the preprocessor or precompiler. For example, the C++ preprocessor can handle conditional compilation directives and string substitution, which allow developers to compile different subsets of the source code for development, production, different DB versions, and so on. But Oracle PL/SQL has been missing this capability?until now.

The recently released Oracle Database10gR2 has a new feature that makes it easy to meet these needs: PL/SQL conditional compilation (CC). PL/SQL CC is not technically a precompiler or preprocessor; it’s functionality that Oracle added to the earliest stages of the existing PL/SQL compilation process. The same engine carries it out along with the other stages. However, PL/SQL is sometimes referred to as a preprocessor, as in the name of the DBMS_PREPROCESSOR package (more on that later).

This article shows you how to use Oracle 10gR2’s new CC feature, how to view the results of conditional compilation with DBMS_PREPROCESSOR, how to leverage this new feature with DBMS_DB_VERSION to create version-independent code, and how to use conditional compilation to create a lightweight debugging framework.

Using PL/SQL Conditional Compilation

PL/SQL conditional compilation is very easy to use. You need to learn just two new types of semantic construct:

  1. Compiler directives
  2. Conditional compilation flags (ccflags), which for the conditional compiler are like “constants” that can be set at a system, session, or individual compilation level (Some default flags are available, and you can supply your own.)

The most basic principle of conditional compilation is that you must be able to compile your code conditionally (i.e., compile certain code IF certain conditions are met, ELSE compile other code). So, the most basic building block for conditional compilation is a compiler directive that works like an if… then… else construct.

In PL/SQL CC, this building block is known as the selection directive. It works very similarly to the if… then… else construct in PL/SQL. The syntax is:

$IF condition $THEN .....[$ELSIF condition $THEN.... ][$ELSE .....]$END    

As you may have guessed, all PL/SQL CC tokens are preceded by a dollar sign ($).

The two key differences between the CC selection directive and the standard PL/SQL if… then… else construct are the following:

  1. The CC selection directive terminates with $END, not $END $IF.
  2. The condition in the CC IF-test has significant restrictions. Since the compiler needs to evaluate condition at compile-time, condition cannot be variable?it must be a constant value at compile-time. Specifically, it must be a static Boolean expression. A good approximation to this concept is an expression that is static at compile-time (see Sidebar 1. Static Boolean Expressions for a more in-depth explanation).

So, for example, this is acceptable:

$IF -4 = 8 $THEN... $END

But this is not, since ?4=TRUNC(sysdate,'MM') is not a static Boolean expression:

$IF -4=TRUNC(sysdate,'MM') $THEN... $END

The IF-test expressions can include package constants (set elsewhere, not in the same package, of course) so long as these are static Boolean expressions themselves, for example:

create or replace package PayrollApp_CC_Constants is	c_SpecialNumber constant PLS_INTEGER := 8;	c_DebugOn constant BOOLEAN := false;end PayrollApp_CC_Constants;....$IF PayrollApp_CC_Constants.c_DebugOn $THEN	debug('Debug On');$ELSIF PayrollApp_CC_Constants.c_SpecialNumber = -4 $THEN	raise;$END....

(Author’s Note: In this and all subsequent examples, debug('X') does not refer to any PL/SQL language element. If not otherwise specified, it refers to a hypothetical procedure, found elsewhere in the hypothetical package being used in the example, which takes a debug message as argument and presumably prints it out or logs it somewhere.)

When using static-expression constants in your IF-test expressions for conditional compilation, it’s best to separate these constants out and keep them all together in a package that contains nothing but such constants. This way, it’s easier to see at a glance what the values of the constants determining your compilation results are. They are also less likely to be confused with regular application constants in the PL/SQL code, which could, for example, lead to such a constant accidentally being set to a non-static value or expression. Finally, separating out the constants means you are guaranteed not to tangle yourself in circular compilation dependencies.

The following example makes it very clear that the conditional compilation selection directives are processed before Oracle tries to compile, or even parse, the rest of the code. Consider this simple procedure:

create or replace procedure testproc isbegin$if TRUE $then  dbms_output.put_line('We got here');$else  RANDOM BAD STUFF, NOT EVEN SYNTACTICALLY CORRECT$endnull;end testproc;

The text of this procedure contains big syntax mistakes (“RANDOM BAD STUFF, NOT EVEN SYNTACTICALLY CORRECT“), but these mistakes are in a branch of the $IF - $THEN - $ELSE that will never be evaluated. And this procedure compiles just fine:

SQL> create or replace procedure testproc is  2  begin  3  $if TRUE $then  4     dbms_output.put_line('We got here');  5  $else  6     RANDOM BAD STUFF, NOT EVEN SYNTACTICALLY CORRECT  7  $end  8  null;  9  end testproc;10 /Procedure created.SQL> show errors procedure testprocNo errors.

Using Conditional Compilation Flags

In the conditional compilation IF-test, and elsewhere in the code, you can also use static expressions that are passed directly to the compiler at compile-time, not set in any PL/SQL code. Three options are available:

  • PL/SQL Compiler initialization parameters, such as NLS_Length_Semantics
  • User-defined conditional compilation flags, or ccflags
  • Oracle’s two predefined ccflags, PLSQL_Unit and PLSQL_Line

You reference these non-PL/SQL expressions using the inquiry directive, or $$. That is, you preface a ccflag or a PL/SQL Compiler initialization parameter with $$. This signals the conditional compilation engine to reads its value in at compile-time, for example:

$IF $$PLSQL_LINE=15 $THEN	debug('That was line 15');$END ;

You set user-defined ccflags by assigning a string of name-value pairs to the Oracle parameter PLSQL_CCFLAGS in the format ‘name:value[,name:value][,name:value]….‘. For example, to set the values of ccflags named “set_debug_on”, “use_10g_features”, and “compile_w_extras”, you would set PLSQL_CCFLAGS as follows:

PLSQL_CCFLAGS = 'set_debug_on:true,use_10g_features:false,compile_w_extras:false' ;

You can set PLSQL_CCFLAGS at the session or system level with ALTER SESSION or ALTER SYSTEM, respectively. Or, you can set PLSQL_CCFLAGS at compile time, for individual package or procedure compilation.

Here’s an example of setting user-defined ccflags at compile time:

SQL> alter package PAYROLL_CALC compile   2> plsql_ccflags =  3> 'no_commit_trans:true,print_debug_msg:true'  4> reuse settings ;

Here’s an example of setting the value of PLSQL_CCFLAGS at the ALTER SESSION level:

SQL> alter session set PLSQL_CCFLAGS =   2> 'set_debug_on:true' ;

And here’s an example of setting the value of PLSQL_CCFLAGS at compile-time:

SQL> alter system set PLSQL_CCFLAGS =   2> 'use_10g_features:false' ;

You can combine package constants with ccflags in the conditional compilation IF-test, for example:

$IF $$DEBUG_ON or PayrollConstants.c_Debug $THEN	debug('Error');$END

You can set the package constant to TRUE during development and FALSE thereafter. After development, you can recompile the package with the ccflag DEBUG_ON set to TRUE whenever it’s necessary for debugging. The following case study looks at this concept in more detail.

Case Study: Lightweight Debug Framework

In the following example of a lightweight debugging framework, a PL/SQL constant, set in a dedicated package, controls whether or not the code is compiled with debugging features. If you set this constant to false, you can override it by setting a separate ccflag to true:

create or replace package DebugConstants is	c_debug constant boolean := true;end;/create or replace package debugpkg is	procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2);end debugpkg;/create or replace package debugpkg is	procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2) is		v_error_string varchar2(32767);	begin		$IF $$DEBUG_ON or DebugConstants.c_debug		$THEN			v_error_string := 'ERROR in ' || p_plsUnit || ', at line ' || p_lineNum || ': ' || p_msg;			dbms_output.put_line(substr(v_error_string,1,255));		$ELSE			null;		$END	end debug;end debugpkg;/

As you can see, if DebugConstants.c_debug is false and the ccflag debug_on is false, then a call to DebugPkg.debug is simply a call to null, which is very lightweight. Let’s create a simple package that uses this debugging framework:

create or replace package testpkg is	procedure run;end;/create or replace package body testpkg is	procedure run is		v_dummy varchar2(1);		begin		dbms_output.put_line('Beginning test run...');		select * into v_dummy from dual;		if v_dummy = 'X' then			debug('Dummy is X!', $$plsql_line, $$plsql_unit);		end if;	end run;end testpkg;

Note that the call to DebugPkg.debug uses the new Oracle-defined ccflags, $$plsql_line and $$plsql_unit. With the value of DebugConstants.c_debug set to true, a call to debug prints out the debug message with the associated unit and line:

SQL> set serverout onSQL> exec testpkg.run ;Beginning test run...ERROR in TESTPKG, at line 19: Dummy is X!PL/SQL procedure successfully completed.

Now change the stored constant c_debug:

create or replace package DebugConstants is    c_debug constant boolean := false;end;

If you run the test procedure after setting the stored constant c_debug to FALSE, no debug information is printed out:

SQL> exec testPkg.run ;Beginning test run...PL/SQL procedure successfully completed.

The debug info is no longer printed out because when the package DebugConstants was recompiled, Oracle remembered the dependency and recompiled DebugPkg without the debugging code.

The Error Directive

PL/SQL conditional compilation includes only three directives. You’ve already seen two of them, the selection directive ($IF... $END) and the inquiry directive ($$). The remaining directive is the error directive, a syntactical element that offers even more help for the developer in need. You can keep unfinished code from compiling and remind yourself what needs to be finished by making the compiler raise an error on incomplete sections.

The syntax of the error directive is very simple:

$ERROR varchar2_string $END

When you compile code that includes an error directive, the PL/SQL compiler raises a PLS-00179 error on your $ERROR line and shows the value of varchar2_string as the error text; for example:

create or replace package testErrDirective is  procedure run;end;/create or replace package body testErrDirective is  procedure run is  begin    dbms_output.put_line('Beginning test run...');    $ERROR      'Oh No! There''s no code at line ' ||      $$plsql_line      || ' in ' || $$plsql_unit    $END  end run;end testpkg;/

The package body will compile with errors, and the error text will show as the message in the error directive:

SQL> show errorsErrors for PACKAGE BODY TESTERRDIRECTIVE:LINE/COL ERROR-------- ------------------------------------------5/3      PLS-00179: $ERROR: Oh No! There's no code         at line 6 in TESTERRDIRECTIVE

If, like me, you’ve paged through old code with your eyebrows slowly creeping toward your hairline as you pass comment after comment that says something like:

--Joe: this doesn't work ; fix before putting into prod!!! 

Then you’ll immediately recognize the utility of the error directive.

Version-Dependent Conditional Compilation

The introduction mentioned that developers often need to be able to compile different code depending on the target database version in order to take advantage of the latest Oracle RDBMS features. To make this even easier to do with PL/SQL conditional compilation, Oracle has accompanied its 10gR2 release with a new PL/SQL-supplied package, DBMS_DB_VERSION. This package is a simple set of constants that hold the version and release numbers. Code that uses DBMS_DB_VERSION might look like this:

...$IF DBMS_DB_VERSION.VERSION=10 $THEN... [version 10 code] ...$ELSE ... [code for earlier versions]...$END...

At compile time, only one of these two branches will be compiled into the package, saving on memory and overhead.

For detailed information on the constants in the DBMS_DB_VERSION package, see the PL/SQL Supplied Packages chapter.

Conditional Compilation on Pre-10gR2 Databases

You might be wondering what good version-dependent conditional compilation is, when PL/SQL conditional compilation is a new 10gR2 feature. The answer is that Oracle has taken the very unusual step of backporting this new feature to earlier releases. PL/SQL conditional compilation is available for 9iR2 in patchsets from 9.2.0.6 onwards, and for 10gR1 in patchsets from 10.1.0.4 onwards. In 9.2.0.6, conditional compilation is disabled by default but can be enabled by setting an underscore (hidden) parameter; in 10.1.0.4, the feature is available by default but can be disabled via the same underscore parameter. (Note that Oracle only supports changing the value of underscore parameters under the direct guidance of Oracle Support.) In 10.2, CC cannot be disabled and the underscore parameter is obsolete.

Viewing CC Results with DBMS_PREPROCESSOR

When your code includes conditional compilation directives, the code in DBA_SOURCE is no longer an exact picture of the compiled code in memory. The text in DBA_SOURCE includes the compiler directives and all the branches of the source. Fortunately, Oracle provides another new supplied package, DBMS_PREPROCESSOR, which lets you see what your code looks like post-compilation.

As a brief example, consider what happens if you execute DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE on the DebugPkg code from the previous lightweight debugging framework. (Recall that you left the stored constant DebugConstants.c_debug set to FALSE.)

First, compile DebugPkg with the debugging code in by setting the ccflag debug_on to TRUE:

SQL> alter package debugpkg compile plsql_ccflags = 'debug_on:true' reuse settings ;Package altered.

Now, run DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE to see what the compiled code looks like:

SQL> begin   2> dbms_preprocessor.print_post_processed_source3>	('PACKAGE BODY','BULKLOAD','DEBUGPKG');4>	end;5>	/package body debugpkg is  procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2) is    v_error_string varchar2(32767);  begin    v_error_string := 'ERROR in ' || p_plsUnit || ', at line ' || p_lineNum || ': ' || p_msg;                     dbms_output.put_line(substr(v_error_string,1,255));  end debug;end debugpkg;PL/SQL procedure successfully completed.

As you can see, the selection directives $IF...$END are absent from the compiled source, and only the code branch with the debug code is included. If you recompile DebugPkg with the ccflag debug_on set to FALSE, you see the other branch instead:

SQL> alter package debugpkg compile plsql_ccflags = 'debug_on:false' reuse settings ;Package altered.SQL> begin   2> dbms_preprocessor.print_post_processed_source6>	('PACKAGE BODY','BULKLOAD','DEBUGPKG');7>	end;8>	/package body debugpkg is  procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2) is    v_error_string varchar2(32767);  begin    null;  end debug;end debugpkg;PL/SQL procedure successfully completed.

Note the following factors when using DBMS_PREPROCESSOR:

  • The stored procedure passed does not need to be valid.
  • You can also pass in an anonymous PL/SQL block, or a PL/SQL table of source lines.
  • An associated function, GET_POST_PROCESSED_SOURCE, returns post-processed source code as a PL/SQL table of source lines, instead of printing it out with DBMS_OUTPUT.PUT_LINE.

If you run DBMS_PREPROCESSOR on code that contains an error directive, the results include an error at the appropriate place:

SQL> begin   2> dbms_preprocessor.print_post_processed_source9>	('PACKAGE BODY','BULKLOAD',10>	'TESTERRDIRECTIVE');11>	end;12>	/package body testErrDirective is  procedure run is  begin    dbms_output.put_line('Beginning test run...');ORA-06550: line 5, column 3:PLS-00179: $ERROR: Oh No! There's no code at line 6 inPL/SQL procedure successfully completed.

Smaller, Faster Run-Time Code Is in Your Future

Conditional compilation is as versatile as it is simple to use. The few CC uses covered here really only scratch the surface. For example, you could customize master code for different clients; you could include separately licensed features in the main code body and simply hide them behind a set-to-false ccflag for customers that haven’t licensed them; and so on.

At minimum, every developer who’s concerned about the cumulative performance impact of a million “if v_debug=true then... else...” calls can now use conditional compilation to scrub these calls completely from production code, while leaving the debug framework in place for future debugging.

I encourage you to start using this versatile new feature today to reap the benefits of smaller, faster run-time code.

devx-admin

devx-admin

Share the Post:
5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1

Copilot Revolution

Microsoft Copilot: A Suit of AI Features

Microsoft’s latest offering, Microsoft Copilot, aims to revolutionize the way we interact with technology. By integrating various AI capabilities, this all-in-one tool provides users with an improved experience that not

AI Girlfriend Craze

AI Girlfriend Craze Threatens Relationships

The surge in virtual AI girlfriends’ popularity is playing a role in the escalating issue of loneliness among young males, and this could have serious repercussions for America’s future. A

AIOps Innovations

Senser is Changing AIOps

Senser, an AIOps platform based in Tel Aviv, has introduced its groundbreaking AI-powered observability solution to support developers and operations teams in promptly pinpointing the root causes of service disruptions

Bebop Charging Stations

Check Out The New Bebob Battery Charging Stations

Bebob has introduced new 4- and 8-channel battery charging stations primarily aimed at rental companies, providing a convenient solution for clients with a large quantity of batteries. These wall-mountable and

Malyasian Networks

Malaysia’s Dual 5G Network Growth

On Wednesday, Malaysia’s Prime Minister Anwar Ibrahim announced the country’s plan to implement a dual 5G network strategy. This move is designed to achieve a more equitable incorporation of both

Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to Russia’s rapid utilization of airborne

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered around improving user experience, streamlining

Price Wars

Inside Hyundai and Kia’s Price Wars

South Korean automakers Hyundai and Kia are cutting the prices on a number of their electric vehicles (EVs) in response to growing price competition within the South Korean market. Many

Solar Frenzy Surprises

Solar Subsidy in Germany Causes Frenzy

In a shocking turn of events, the German national KfW bank was forced to discontinue its home solar power subsidy program for charging electric vehicles (EVs) after just one day,

Electric Spare

Electric Cars Ditch Spare Tires for Efficiency

Ira Newlander from West Los Angeles is thinking about trading in his old Ford Explorer for a contemporary hybrid or electric vehicle. However, he has observed that the majority of

Solar Geoengineering Impacts

Unraveling Solar Geoengineering’s Hidden Impacts

As we continue to face the repercussions of climate change, scientists and experts seek innovative ways to mitigate its impacts. Solar geoengineering (SG), a technique involving the distribution of aerosols

Razer Discount

Unbelievable Razer Blade 17 Discount

On September 24, 2023, it was reported that Razer, a popular brand in the premium gaming laptop industry, is offering an exceptional deal on their Razer Blade 17 model. Typically

Innovation Ignition

New Fintech Innovation Ignites Change

The fintech sector continues to attract substantial interest, as demonstrated by a dedicated fintech stage at a recent event featuring panel discussions and informal conversations with industry professionals. The gathering,

Import Easing

Easing Import Rules for Big Tech

India has chosen to ease its proposed restrictions on imports of laptops, tablets, and other IT hardware, allowing manufacturers like Apple Inc., HP Inc., and Dell Technologies Inc. more time