1 2 Previous Next 20 Replies Latest reply: Jun 1, 2011 5:43 AM by 859035 RSS

    Null value handling in LOVs

    VANJ
      Has null value handling improved any in HTMLDB 1.6?

      If my LOV has "Display null"=Yes (default null value is %null%) and I need to pass in a database NULL to a After Submit process, I need to do add a After Submit computation for each of these LOVs with something like

      CASE WHEN :P1_ITEM='%null%' THEN NULL ELSE :P1_ITEM END

      I have a couple dozen LOVs on a page and it is getting tiring to go and do this on each and every one!

      Is there a better way?

      Thanks
        • 1. Re: Null value handling in LOVs
          VANJ
          Repost. Thanks
          • 2. Re: Null value handling in LOVs
            464782
            Sorry, I'm pretty late :)...
            In fact, I have the same question according to HTMLDB 2.0. We're using HTMLDB since v1.4 and had the same problem all the time... Has anyone found a solution to this?

            thx Alex
            • 3. Re: Null value handling in LOVs
              VANJ
              I just ended up moving it to row-level before insert triggers on the underlying tables.
              :new.col1 := replace(:new.col1,'%null%');
              ...
              or something like that
              • 4. Re: Null value handling in LOVs
                523558
                I'm working on this now... can you explain the trigger you placed??

                I have a numeric value on my form trying to save '%null%' into a number column... so I'm getting an invalid number error.
                • 5. Re: Null value handling in LOVs
                  135285
                  Hi,

                  if you use APEX 2.2 you can use the following generic solution.

                  Create a Application Process (Shared Components\Application Process)
                  -) Name: RemoveNulls
                  -) Sequence: 0 (should be a number before any of your page level processes)
                  -) On submit: After Page Submission - Before Computations and Validations
                  -) Process Text
                  BEGIN
                      FOR rItem IN
                        ( SELECT ITEM_NAME
                            FROM APEX_APPLICATION_PAGE_ITEMS
                           WHERE APPLICATION_ID   = TO_NUMBER(:APP_ID)
                             AND PAGE_ID          IN (TO_NUMBER(:APP_PAGE_ID), 0)
                             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;
                  This application process will run for every page and will remove the %null% for all the Lovs.

                  Hope this helps
                  Patrick
                  ----------------------------------------------
                  http://inside-apex.blogspot.com
                  • 6. Re: Null value handling in LOVs
                    60437
                    Patrick - Nice technique. You could even include page zero items in the logic. Also, we always express '%null%' as:
                      '%null' || '%'
                    to prevent it from getting nulled out during Builder save actions.

                    Scott
                    • 7. Re: Null value handling in LOVs
                      135285
                      Scott,

                      thanks for the info with the page zero items!

                      BTW, I just noticed that for select lists in a tabular form, no %null% value is used when the "Display Null" property is set to yes. Instead no value (null) is rendered into the HTML file. Is there a special reason for that different behavior, compared to item select lists?

                      Thanks
                      Patrick
                      -------------------------------------------------------------------------------------
                      Check out my APEX-Blog: http://inside-apex.blogspot.com
                      • 8. Re: Null value handling in LOVs
                        135285
                        Just to let you know. I took Scott's suggestion about the page 0 items and have included the clearing of %null% into my library which I have created for Generic solution for cascading lovs. See my blog posting Display Null = "Yes" and the %null% problem for details.

                        Patrick
                        -------------------------------------------------------------------------------------
                        Check out my APEX-Blog: http://inside-apex.blogspot.com
                        • 9. Re: Null value handling in LOVs
                          Oliver L
                          really nice solution from patrick wolf.

                          but why does oracle never fixed this problem? Iam using 3.1.2 and i have still to work with this workaround.
                          Is there any reason why it can't be implemented with submitting NULL value instead of %null%
                          • 10. Re: Null value handling in LOVs
                            631955
                            Hi everyone,

                            I tried to get my tabular form LOVs to submit NULL values. I did it through creating the application process that goes through all the page item values and substitutes, but it does not seem to work for items from my tabular form. Is there a solution similar to the application process that would be able to solve my problem for all the pages of an app?

                            Thanks,

                            Jakub
                            • 11. Re: Null value handling in LOVs
                              34946
                              yes, and this is also mentioned in the following thread
                              cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
                              but there isnt always the opportunity to implement these workarounds

                              Regards,

                              Anthony Harper
                              • 12. Re: Null value handling in LOVs
                                Oliver L
                                I tested Application Express 4.0.0.00.25 (EA Release 2) on http://tryapexnow.com with a select list and the value which is commited is still %null%.
                                Why isn't it still not fixed?
                                I reported this as a bug and iam hoping that the final release will fix it.
                                • 13. Re: Null value handling in LOVs
                                  Gussay
                                  Hi Patrick,
                                  we are currently moving over to Apex 4.02 and are doing some smoke tests on our existing apps.

                                  I have come across an issue with your generic solution to the %null% issue above (incidently, for a long time it's the first piece of code I include when I start a new app... thank you!)

                                  We found that a lot of our SQL Query (PL/SQL function body returning SQL query) reports were failing due to the session state of the LOVs still having a session state of %null% even after the RemoveNulls application process had run.

                                  After debugging your generic process we found that the LOV_NULL_VALUE in the APEX_APPLICATION_PAGE_ITEMS view has changed from NULL to '%null%'.
                                  so the fix was as below:-
                                  BEGIN
                                      FOR rItem IN
                                        ( SELECT ITEM_NAME
                                            FROM APEX_APPLICATION_PAGE_ITEMS
                                           WHERE APPLICATION_ID   = TO_NUMBER(:APP_ID)
                                             AND PAGE_ID          IN (TO_NUMBER(:APP_PAGE_ID), 0)
                                             AND LOV_DISPLAY_NULL = 'Yes'
                                             AND LOV_DEFINITION   IS NOT NULL
                                  -- change here
                                             AND LOV_NULL_VALUE  = '%null' || '%' 
                                        )
                                      LOOP
                                          IF V(rItem.ITEM_NAME) = '%null' || '%'
                                          THEN
                                              Apex_Util.set_session_state(rItem.ITEM_NAME, NULL);
                                          END IF;
                                      END LOOP;
                                  END;
                                  Cheers,

                                  Gus.. (always a fan of your work!)
                                  • 14. Re: Null value handling in LOVs
                                    Oliver L
                                    In all Version after APEX 4.0.X the bug is fixed.
                                    You don't have to use the script described above, the value null is now supported without anything extra code.

                                    Here you can check it
                                    http://apex.oracle.com/pls/apex/f?p=20211

                                    There is a PL/SQL Validiation and a select list where you can choose 1, %null% or null.
                                    The Validation says if you have a null value or not and prints the value.
                                    1 2 Previous Next