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:
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
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;