2021 – A Better Uniqueness Function

Iudith Mentzel noticed a problem in my OS_LIST_UNIQUE function. It relied on the Ordering System (OS) number being incremental. For example [3,2,1] would not be considered a unique list. The problem lay in the following steps:

  1. Load the passed JSON Array into a table 1
  2. Loop through table 1 sequentially to load unique values into table 2
  3. Compare table 2 to the passed JSON Array

So if I passed in 3,2,1 it became 1,2,3 which is not equal to 3,2,1.

Having verified the problem I set about fixing the code.

My first thoughts were to somehow not sort the values passed in or somehow resort them afterwards. But that seemed to be way too complex so I did what I always do in these situations. I took a nap.

I awoke with a fresh mind and realized the best thing to do did not involve sorting at all. I just had to augment step 2 (above) to bail and return 0 if it found an element already existed. For example if it already populated element 3 and another 3 showed up then the whole array is deemed non-unique. This probably made the code faster too!

Here is the code.

CREATE OR REPLACE FUNCTION os_list_unique ( p_os_list VARCHAR2 )
                    RETURN NUMBER
                    DETERMINISTIC
                    RESULT_CACHE AS

  v_local_list VARCHAR2(1000);
  v_item_count BINARY_INTEGER;
  v_array DBMS_UTILITY.LNAME_ARRAY;
  TYPE v_tab_t IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
  v_tab v_tab_t;

BEGIN

  -- remove the square brackets from the JSON object passed in
  v_local_list := REPLACE(p_os_list,'[','');
  v_local_list := REPLACE(v_local_list,']','');

  -- add x's so the table can load correctly and load the table
  v_local_list := regexp_replace(v_local_list,'(^|,)','\1x');
  DBMS_UTILITY.COMMA_TO_TABLE(v_local_list,v_item_count,v_array);

  -- loop through recording the unique values only
  FOR counter IN 1..v_item_count LOOP
    -- if we already have an entry for this number then
    -- the list is not unique so bail now!
    IF v_tab.EXISTS(v_array(counter)) THEN
      RETURN(0);
    ELSE
      v_tab(v_array(counter)) := 1;
    END IF;
  END LOOP;

  RETURN(1);

END;

And some verification…

BEGIN
  DBMS_OUTPUT.PUT_LINE(os_list_unique('[1,2,3]')); -- unique
  DBMS_OUTPUT.PUT_LINE(os_list_unique('[3,2,1]')); -- unique
  DBMS_OUTPUT.PUT_LINE(os_list_unique('[1,1,2,3]')); -- not unique
  DBMS_OUTPUT.PUT_LINE(os_list_unique('[3,2,1,4,5,6,7,2]')); -- not unique
END;

And the results…

1
1
0
0

Thanks Iudith!

Leave a Comment