SAGE Computing Services Customised Oracle Training Workshops and

SAGE Computing Services Customised Oracle Training Workshops and Consulting Creative Conditional Compilation … and “raising the bar” with your PL/SQL Scott Wesley Systems Consultant

The example everybody’s seen… FUNCTION qty_booked(p_resource IN VARCHAR 2 , p_date IN DATE) RETURN NUMBER $IF dbms_db_version. ver_le_10 $THEN $ELSE RESULT_CACHE $END IS li_total PLS_INTEGER : = 0; BEGIN SELECT SUM(b. qty) INTO li_total FROM bookings b, events e WHERE p_date BETWEEN e. start_date AND e. end_date AND b. resource = p_resource; RETURN li_total END qty_booked;

• PL/SQL User’s Guide & Reference 10 g Release 2 – Fundamentals of the PL/SQL Language • Conditional Compilation

Availability • 11 g Release 1 • 10 g Release 2 – Enabled out of the box • 10. 1. 0. 4 – Once patched, enabled by default – Disable using “_parameter” • 9. 2. 0. 6 – Once patched, disabled by default – Enable using “_parameter”

Catch 22 INDICES OF

Catch 22 INDICES OF Conditional Compilation Patch

Performance Readability Facilitates removal of unnecessary code at compile time It's cool! Accuracy Testing

Semantics Selection Directives $IF boolean_static_expression $THEN text [ $ELSIF boolean_static_expression $THEN text ] [ $ELSE Directives text ] Inquiry $END DBMS_OUTPUT. PUT_LINE($$PLSQL_LINE); Error Directives ALTER SESSION SET PLSQL_CCFLAGS='max_sentence: 100'; sentence > $$max_sentence $IFIF$$PLSQL_OPTIMIZE_LEVEL != 2 THEN $ERROR 'intensive_program must be compiled with maximum optimisation' $END

Inquiry Directives

<< anon >> BEGIN DBMS_OUTPUT. PUT_LINE('Unit: '||$$PLSQL_UNIT); DBMS_OUTPUT. PUT_LINE('Line: '||$$PLSQL_LINE); END anon; / Unit: Line: 4

> CREATE OR REPLACE PROCEDURE sw_test IS BEGIN DBMS_OUTPUT. PUT_LINE('Unit: '||$$PLSQL_UNIT); DBMS_OUTPUT. PUT_LINE('Line: '||$$PLSQL_LINE); END sw_test; / Procedure created. > exec sw_test Unit: SW_TEST Line: 4

ALTER SESSION SET PLSQL_CCFLAGS = 'max_sentence: 100'; Session altered.

> BEGIN IF p_sentence < $$max_sentence THEN DBMS_OUTPUT. PUT_LINE('Parole Available'); ELSE DBMS_OUTPUT. PUT_LINE('Life'); END IF; END; / Life

ALTER SYSTEM SET PLSQL_CCFLAGS = 'VARCHAR 2_SIZE: 100, DEF_APP_ERR: -20001'; DECLARE lc_variable_chr VARCHAR 2($$VARCHAR 2_SIZE); e_def_app_err EXCEPTION; PRAGMA EXCEPTION_INIT (e_def_app_err, $$DEF_APP_ERR); BEGIN --> rest of your code END anon; /

First Demo: Post-processed Source Demo: cc 1. sql cc 2. sql

Reuse Settings

ALTER SYSTEM SET PLSQL_CCFLAGS = 'MY_PI: 314'; CREATE OR REPLACE PROCEDURE universe_alpha IS CREATE OR REPLACE PROCEDURE universe_gamma IS BEGIN CREATE OR REPLACE PROCEDURE universe_oz IS BEGIN DBMS_OUTPUT. PUT_LINE('Alpha pi = '||$$my_pi/100); BEGIN pi = '||$$my_pi/100); END; DBMS_OUTPUT. PUT_LINE('Gamma END; DBMS_OUTPUT. PUT_LINE('Oz pi = '||$$my_pi/100); END; ALTER PROCEDURE universe_alpha COMPILE PLSQL_CCFLAGS = 'MY_PI: 289' REUSE SETTINGS; ALTER PROCEDURE universe_gamma COMPILE > BEGIN PLSQL_CCFLAGS = 'MY_PI: 423' universe_alpha; REUSE SETTINGS; universe_gamma; universe_oz; END; / Alpha pi = 2. 89 Gamma pi = 4. 23 Oz pi = 3. 14

Second Demo: Directive Usage Demo: cc 3. sql cc 4. sql cc 5. sql

Some versioning examples?

Using new version code today $IF dbms_db_version. ver_le_10 $THEN -- version 10 and earlier code $ELSIF dbms_db_version. ver_le_11 $THEN -- version 11 code $ELSE -- version 12 and later code $END

10. 1 vs 10. 2 dbms_output

CREATE OR REPLACE PROCEDURE sw_debug (p_text VARCHAR 2) IS $IF $$sw_debug_on $THEN l_text VARCHAR 2(32767); $END BEGIN $IF $$sw_debug_on $THEN -- Let’s provide debugging info $IF dbms_db_version. ver_le_10_1 $THEN -- We have to truncate for <= 10. 1 l_text : = SUBSTR(p_text, 1 , 200); $ELSE l_text : = p_text; $END DBMS_OUTPUT. PUT_LINE(p_text); $ELSE -- No debugging NULL; $END sw_debug; IN

CREATE OR REPLACE PROCEDURE sw_debug (p_text VARCHAR 2) IS $IF $$sw_debug_on $THEN l_text VARCHAR 2(32767); $END BEGIN $IF $$sw_debug_on $THEN -- Let’s provide debugging info $IF dbms_db_version. ver_le_10_1 $THEN -- We have to truncate for <= 10. 1 l_text : = SUBSTR(p_text, 1 , 200); $ELSE l_text : = p_text; $END DBMS_OUTPUT. PUT_LINE(p_text); $ELSE -- No debugging NULL; $END sw_debug; IN

CREATE OR REPLACE PROCEDURE sw_debug (p_text VARCHAR 2) IS $IF $$sw_debug_on $THEN l_text VARCHAR 2(32767); $END BEGIN $IF $$sw_debug_on $THEN -- Let’s provide debugging info $IF dbms_db_version. ver_le_10_1 $THEN -- We have to truncate for <= 10. 1 l_text : = SUBSTR(p_text, 1 , 255); $ELSE l_text : = p_text; $END DBMS_OUTPUT. PUT_LINE(p_text); $ELSE -- No debugging NULL; $END sw_debug; IN

10 g vs 11 g result_cache FUNCTION quantity_ordered (p_item_id IN items. item_id%TYPE) RETURN NUMBER $IF dbms_version. ver_le_10 $THEN -- nothing $ELSE RESULT_CACHE $END IS BEGIN. . .

9 i vs 10 g Bulk Insert

CREATE OR REPLACE PACKAGE BODY sw_bulk_insert IS PROCEDURE sw_insert (sw_tab IN t_sw_tab) IS BEGIN $IF dbms_db_version. ver_le_9 $THEN DECLARE l_dense t_sw_tab; ln_index PLS_INTEGER : = sw_tab. FIRST; BEGIN << dense_loop >> WHILE (l_index IS NOT NULL) LOOP l_dense(l_dense. COUNT + 1) : = sw_tab(l_index); l_index : = sw_tab. NEXT(l_index); END LOOP dense_loop; FORALL i IN 1. . l_dense. COUNT INSERT INTO sw_table VALUES l_dense(i); END; $ELSE FORALL i IN INDICES OF sw_tab INSERT INTO sw_table VALUES sw_tab(i); $END sw_insert; END sw_bulk_insert;

CREATE OR REPLACE PACKAGE BODY sw_bulk_insert IS PROCEDURE sw_insert (sw_tab IN t_sw_tab) IS BEGIN $IF dbms_db_version. ver_le_9 $THEN DECLARE l_dense t_sw_tab; ln_index PLS_INTEGER : = sw_tab. FIRST; BEGIN << dense_loop >> WHILE (l_index IS NOT NULL) LOOP l_dense(l_dense. COUNT + 1) : = sw_tab(l_index); l_index : = sw_tab. NEXT(l_index); END LOOP dense_loop; FORALL i IN 1. . l_dense. COUNT INSERT INTO sw_table VALUES l_dense(i); END; $ELSE FORALL i IN INDICES OF sw_tab INSERT INTO sw_table VALUES sw_tab(i); $END sw_insert; END sw_bulk_insert;

CREATE OR REPLACE PACKAGE BODY sw_bulk_insert IS PROCEDURE sw_insert (sw_tab IN t_sw_tab) IS BEGIN $IF dbms_db_version. ver_le_9 $THEN DECLARE l_dense t_sw_tab; ln_index PLS_INTEGER : = sw_tab. FIRST; BEGIN << dense_loop >> WHILE (l_index IS NOT NULL) LOOP l_dense(l_dense. COUNT + 1) : = sw_tab(l_index); l_index : = sw_tab. NEXT(l_index); END LOOP dense_loop; FORALL i IN 1. . l_dense. COUNT INSERT INTO sw_table VALUES l_dense(i); END; $ELSE FORALL i IN INDICES OF sw_tab INSERT INTO sw_table VALUES sw_tab(i); $END sw_insert; END sw_bulk_insert;

Paradigm Examples

Latent debugging code

CREATE OR REPLACE PACKAGE pkg_debug IS debug_flag CONSTANT BOOLEAN : = FALSE; END pkg_debug; / CREATE OR REPLACE PROCEDURE sw_proc IS BEGIN $IF pkg_debug_flag $THEN dbms_output. put_line ('Debugging Details'); $END sw_proc; /

Assertions ?

Testing Aid Development tool In-line Documentation “Assertions should be used to document logically impossible situations — if the ‘impossible’ occurs, then something fundamental is clearly wrong. This is distinct from error handling. ” Run-time Cost

Latent Assertions ?

“The removal of assertions from production code is almost always done automatically. It usually is done via conditional compilation. ” www. answers. com/topic/assert

$IF $$asserting -- individual program unit OR CC_assertion. asserting -- entire application $THEN IF p_param != c_pi*r*r THEN raise_application_error(. . END IF; $END

Testing subprograms only in package body

CREATE PACKAGE universe IS PROCEDURE create_sun; CREATE PACKAGE BODY universe IS PROCEDURE create_planets; -- Private -- CC test procedure PROCEDURE orbit IS. . END; PROCEDURE test_orbit; -- Public END universe; PROCEDURE create_sun IS. . END; PROCEDURE create_planets IS. . END; -- Testers PROCEDURE test_orbit IS BEGIN $IF $$testing $THEN orbit; $ELSE RAISE program_error; $END test_orbit; END universe;

CREATE PACKAGE BODY universe IS CREATE PACKAGE universe IS -- Private PROCEDURE create_sun; PROCEDURE orbit IS. . END; PROCEDURE create_planets; -- Public -- CC test sequence PROCEDURE create_sun IS. . END; PROCEDURE test_run; PROCEDURE create_planets IS. . END; END universe; -- Test sequence PROCEDURE test_run IS BEGIN $IF $$testing $THEN create_sun; create_planets; orbit; $ELSE RAISE program_error; $END test_run; END universe;

Mock objects

FUNCTION get_emp(p_emp_id IN emp_id%TYPE) RETURN t_emp IS l_emp t_emp; BEGIN $IF $$mock_emp $THEN l_emp. emp_name : = 'Scott'; . . RETURN l_emp; $ELSE SELECT * FROM emp INTO l_emp WHERE emp_id = p_emp_id; RETURN l_emp; $END get_emp;

Comparing competing implementations during prototyping

PROCEDURE xyz IS $IF $$alternative = 1 $THEN -- varray declaration $ELSIF $$alternative = 2 $THEN -- nested table declaration $END $IF $$alternative = 1 $THEN BEGIN -- first verbose solution that $IF $$alternative = 1 $THEN tosimple mind varray solution -$ELSIF $$alternative = 2 $THEN -- elegant nested table = solution $ELSIF $$alternative 2 $THEN $END -- some crazy idea you came up END xyz; at 3 am you need to try out $END came with

Component Based Installation

PACKAGE BODY core IS PROCEDURE execute_component(p_choice IN VARCHAR 2) IS BEGIN CASE p_choice -- Base is always installed. WHEN 'base' THEN base. main(); $IF CC_licence. cheap_installed $THEN WHEN 'cheap' THEN cheap. main(); $END. . . $IF CC_licence. pricey_installed $THEN WHEN 'pricey' THEN pricey. main(); $END CASE; EXCEPTION WHEN case_not_found THEN dbms_output. put_line('Component '||p_choice||' is not installed. '); END execute_component; END core;

Get It Right with the Error Directive

$IF $$PLSQL_OPTIMIZE_LEVEL != 2 $THEN $ERROR 'intensive_program must be compiled with maximum optimisation' $END

BEGIN. . . /* * * Note to self: Must remember to finish this bit * */. . . END;

BEGIN. . . -- Jacko: this doesn’t work, fix before moving to prod!. . . END;

1 2 3 4 5 6 7 8 9 10 11 12 CREATE PROCEDURE process_court_outcome IS BEGIN IF lr_victim. age > 18 THEN send_to_prison(lr_victim); ELSE $ERROR 'Waiting for business to advise '|| $$PLSQL_UNIT||' line: '||$$PLSQL_LINE $ENDProcedure created with compilation errors. Warning: END IF; END SQL> process_court_outcome; sho err / LINE/COL ERROR ------------------------ 6/6 PLS-00179: $ERROR: Waiting for business to advise PROCESS_COURT_OUTCOME line: 8

CREATE OR REPLACE PACKAGE pkg_debug IS debug_flag CONSTANT BOOLEAN : = FALSE; $IF $$CC_dev_notes $THEN PROCEDURE insert_dev_note(p_note IN VARCHAR 2 , p_unit IN VARCHAR 2 , p_line IN VARCHAR 2) IS PRAGMA AUTONOMOUS TRANSACTION; BEGIN INSERT INTO dev_notes (note, unit, line) VALUES (p_note, p_unit, p_line); END insert_dev_note; $END pkg_debug; /

CREATE PROCEDURE process_court_outcome IS BEGIN IF lr_victim. age > 18 THEN send_to_prison(lr_victim); ELSE pkg_debug. insert_dev_note ('Waiting for business to advise' , $$PLSQL_UNIT , $$PLSQL_LINE); END IF; END process_court_outcome; /

Good Practices

Inquiry directives have null values for normal behaviour $IF $$cc_flag = 'x' $THEN cc_code; $END

Choose restriction type carefully $IF $$debug_on OR module_y. cc_debug $THEN sw_debug('Error'); $END

but there’s always this. . .

SQL> define debug=/* begin &debug select 'conditionally' into : c 1 from dual; -- */ select 'always' into : c 2 from dual; end; /

SAGE Computing Services Customised Oracle Training Workshops and Consulting Questions and Answers? Presentations are available from our website: http: //www. sagecomputing. com. au enquiries@sagecomputing. com. au scott. wesley@sagecomputing. com. au