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:
- Load the passed JSON Array into a table 1
- Loop through table 1 sequentially to load unique values into table 2
- 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