Browse DevX
Sign up for e-mail newsletters from DevX


Streamline Your Oracle Production Code with Conditional Compilation : Page 4

Every Oracle PL/SQL developer concerned about the performance impact of repeated if... then... else calls can use conditional compilation to scrub these calls from production code. And that's just the beginning.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 on SQL> 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 errors Errors 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.

Thanks for your registration, follow us on our social networks to keep up-to-date