How to resolve %null% issue in APEX LOVs

Patrick Wolf mentioned this at ODTUG Kaleidoscope this year.

After you implement your first LOV in an APEX application you'll quickly learn about the %null% problem. APEX substitutes an empty string for Null return value as %null%.

They're several workarounds, like using "-1" as the NULL value. Or modifying your query using "'%' || 'null%'". For example:

1 2 3 4 5 6

SELECT ename,        empno   FROM emp  WHERE empno = DECODE (:p_empno, '%' || 'null%', empno, NULL, empno, :p_empno)

Instead of using workarounds you can convert %null% to NULL (empty string) by creating the following application process:

Application Process: AP_SET_LOV_NULLS
Process Point: On Submit - Before Computations and Validations

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

BEGIN   FOR x IN (SELECT *               FROM (SELECT item_name                       FROM apex_application_page_items aapi                      WHERE aapi.application_id = :app_id                        AND aapi.page_id = :app_page_id                        AND LOWER (aapi.lov_display_null) = 'yes'                        AND aapi.lov_definition IS NOT NULL                        AND aapi.lov_null_value IS NULL                        AND ROWNUM > 0) x              WHERE LOWER (v (x.item_name)) = '%' || 'null%') LOOP     apex_util.set_session_state (x.item_name, NULL);   END LOOP; END;