|
APEX Null Value Handling in LOVs
Application Express (APEX) supports assigning Lists of Values (LOVs) to a page item or report field. The configuration in APEX is demonstrated in the figure below. These LOVs can be static or dynamic and support null values. The default behavior for a null return value is to pass %null% to the insert or update statement. Passing %null% into a number column results in the ORA-20001: Invalid numeric value error. This tech tip demonstrates an APEX v2.2 generic solution to overcome the invalid numeric value errors.
Several solutions exist for handling the %null% value. One solution is to add a trigger on the table to remove the %null% and replace it with a true NULL. However, a trigger would have to be added to every table and column that a LOV is attached thus creating a lot of work. Another more generic solution exists by using an Application Process. A single Application Process runs PL/SQL logic at specific points for each page in an application.
First create an Application Process, which can be found in the Shared Components Logic section.
Create the Application Process with any name you choose. For this example, I picked RemoveNullValues. The sequence needs to be a value less than any of your page level processes. A value of 0 should handle this situation. The point of execution should be "On Submit: After Page Submission - Before Computations and Validations".
Enter the process text for removing the %null% values and an error message should something go terribly wrong. The PL/SQL for doing the removal would be:
BEGIN
FOR rItem IN
( SELECT ITEM_NAME
FROM APEX_APPLICATION_PAGE_ITEMS
WHERE APPLICATION_ID = TO_NUMBER(:APP_ID)
AND PAGE_ID = TO_NUMBER(:APP_PAGE_ID)
AND LOV_DISPLAY_NULL = 'Yes'
AND LOV_DEFINITION IS NOT NULL
AND LOV_NULL_VALUE IS NULL
)
LOOP
IF V(rItem.ITEM_NAME) = '%null%'
THEN
Apex_Util.set_session_state(rItem.ITEM_NAME, NULL);
END IF;
END LOOP;
END;
Do not assign conditional processing to the Application Process as we want the process to always be executed. Now when every page is executed in APEX the %null% values will be removed from the LOVs and replaced with a true null.
Mark Mortensen
Solutions Architect
For additional questions regarding this tip contact techtips@sagelogix.com.
|
