This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jun 1, 2011 3:43 AM by 859035 RSS

Null value handling in LOVs

VANJ Journeyer
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    Repost. Thanks
  • 2. Re: Null value handling in LOVs
    464782 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    OliverL Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    OliverL Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    OliverL Newbie
    Currently Being Moderated
    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