7 Replies Latest reply: Sep 16, 2013 7:39 PM by PPlatt RSS

    How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?

    PPlatt

      I want to validate 2 shuttle list selections for equality. for example

      1:2:5:7:18:3

      and

      1:5:7:2:3:18

      should be treated as equal and therefore valid(i.e. same selections but in different order)

      while

      1:2:5:7:18:3

      and

      1:2:18:9 are not equal and would fail validation.

      If I use string_to_table() function on both lists then I would need to sort the associative arrays and then compare them for equality.  Any suggestions on how best to do this?

      thanks in advance

      PaulP

        • 1. Re: How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?
          fac586

          Create a function that converts delimited strings into nested tables rather than associative arrays. Comparing them is then trivial.

          • 2. Re: How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?
            PPlatt

            Thanks fac586

            Any chance of pointing me to where I can see the code for your suggested solution? Particularly comparing nested tables.

            thanks

            PaulP

            • 3. Re: How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?
              PPlatt

              I've got this code that will work perfectly for me but there is one small sticky point. I don't know how to transform a multiselect/shuttle box string in the format of  '1:2:3:4:5:6:7'  to the numbers separated by a comma (1,2,3,4,5,6,7) for use in the constructor for the nested table. I tried using "replace('1:2:3:4:5:6',':',',')" but it complains as it is still a string even though it looks like a list of numbers separated by ','s .

              Is there another way of getting the multi-select strings into the 2 nested tables besides using the constructor syntax below? Any ideas how to resolve this one hiccup?

               

              declare
                  type nt is table of number;
                  nt1 nt;
                  nt2 nt;
                begin
                    nt1 := nt(1,2,3,4,5,6,7);
                    nt2 := nt(1,3,2,5,4,6,7);
                     if nt1 = nt2 then
                        dbms_output.put_line('nt2 is the same nested table as nt1');
                    else
                        dbms_output.put_line('nt2 is a different nested table from nt1');
                    end if;
                end;

               

              anonymous block completed

              nt2 is the same nested table as nt1

               

              thanks in advance

              PaulP

              • 4. Re: How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?
                fac586

                PPlatt wrote:

                 

                I've got this code that will work perfectly for me but there is one small sticky point. I don't know how to transform a multiselect/shuttle box string in the format of  '1:2:3:4:5:6:7'  to the numbers separated by a comma (1,2,3,4,5,6,7) for use in the constructor for the nested table. I tried using "replace('1:2:3:4:5:6',':',',')" but it complains as it is still a string even though it looks like a list of numbers separated by ','s .

                You can't do that without using dynamic PL/SQL, which is horrible.

                Is there another way of getting the multi-select strings into the 2 nested tables besides using the constructor syntax below? Any ideas how to resolve this one hiccup?

                Create a function equivalent to the apex_util.string_to_table method that returns a nested table rather than an associative array (I have no idea why APEX doesn't include this: there are so many ways in which it is useful...)

                 

                create or replace function string_to_coll (
                    p_string    in varchar2
                  , p_separator in varchar2 := ',') return sys.ODCIVarchar2List
                is
                
                  l_table apex_application_global.vc_arr2;
                  l_list  sys.ODCIVarchar2List := new sys.ODCIVarchar2List();
                
                begin
                
                  l_table := apex_util.string_to_table(p_string, p_separator);
                
                  l_list.extend(l_table.count());
                
                  for i in 1..l_table.count()
                  loop
                    l_list(i) := l_table(i);
                  end loop;
                
                  return l_list;
                
                end;
                
                
                

                 

                For demonstration purposes this uses the apex_util.string_to_table to parse the string, and the pre-defined sys.ODCIVarchar2List collection type. In real life you'd create your own type, and parse the string in one pass for efficiency. And of course the function would be in a package, not standalone.


                declare
                    nt1 sys.ODCIVarchar2List;
                    nt2 sys.ODCIVarchar2List;
                  begin
                      nt1 := string_to_coll('1,2,3,4,5,6,7', ',');
                      nt2 := string_to_coll('1,3,2,5,4,6,7', ',');
                      if nt1 = nt2 then
                          dbms_output.put_line('nt2 is the same nested table as nt1');
                      else
                          dbms_output.put_line('nt2 is a different nested table from nt1');
                      end if;
                  end;
                
                
                
                • 5. Re: How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?
                  PPlatt

                  Thanks fac5886 I appreciate your time and sharing your code.

                  Unfortunately an error is raised when executing the anonymous block.

                  "ORA-06550: line 7, column 10:"

                  PLS-00306: wrong number or types of arguments in call to '='

                  It seems that the simple test for equality is not working with sys.ODCIVarchar2List type lists.

                  Any suggestions?

                  PaulP

                  • 6. Re: How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?
                    fac586

                    Sorry, the equality check works with PL/SQL nested table types:

                     

                    declare
                    
                      type nt is table of varchar2(4000);
                    
                      nt1 nt;
                      nt2 nt;
                    
                    function string_to_coll (
                          p_string    in varchar2
                        , p_separator in varchar2 := ',')
                        return nt
                      is
                        l_table apex_application_global.vc_arr2;
                        l_list  nt := new nt();
                      begin
                        l_table := apex_util.string_to_table(p_string, p_separator);
                        l_list.extend(l_table.count());
                        for i in 1..l_table.count()
                        loop
                          l_list(i) := l_table(i);
                        end loop;
                        return l_list;
                      end string_to_coll;
                    
                    begin
                    
                      nt1 := string_to_coll('1,2,3,4,5,6,7');
                      nt2 := string_to_coll('1,3,2,5,4,6,7');
                    
                      if nt1 = nt2
                      then
                        dbms_output.put_line('nt2 is the same nested table as nt1');
                      else
                        dbms_output.put_line('nt2 is a different nested table from nt1');
                      end if;
                    
                    end;
                    

                     

                    You'll have to use a package to contain the PL/SQL nested table definition so the type is available to the string_to_coll function and any APEX blocks that call it.

                     

                    I picked up the string_to_coll function from a previous example where a SQL type was required. These have to be compared using SQL:

                     

                    declare
                    
                      nt1 sys.ODCIVarchar2List;
                      nt2 sys.ODCIVarchar2List;
                      n number;
                    
                    begin
                    
                      nt1 := string_to_coll('1,2,3,4,5,6,7', ',');
                      nt2 := string_to_coll('1,3,2,5,4,6,7', ',');
                    
                      select
                          count(*)
                      into
                          n
                      from
                          dual
                      where
                          exists (select column_value from table(nt1)
                                  minus
                                  select column_value from table(nt2));
                    
                      if n = 0
                      then
                        dbms_output.put_line('nt2 is the same nested table as nt1');
                      else
                        dbms_output.put_line('nt2 is a different nested table from nt1');
                      end if;
                    
                    end;
                    
                    • 7. Re: How to compare 2 APEX multi-select/shuttle box lists (e.g.1:2:3 and 3:1:2) for equality?
                      PPlatt

                      Thanks for all you help but I've decided to stay with APEX string_to_table arrays and keep it simple.  I decided to return Varchar2 ('TRUE','FALSE') rather than boolean (TRUE,FALSE) so I could use the function in an SQL WHERE clause.

                      Here is my working solution

                       

                      create or replace FUNCTION CheckMultiSelectStringsEqual(
                            p_string1    IN VARCHAR2 ,
                            p_string2    IN VARCHAR2 ,
                            p_separator IN VARCHAR2)
                          RETURN varchar2
                      ------------------------------------------------------------------------------------------------------------------
                      -- This function accepts 2 multi-select/shuttle box strings in the format '1:2:3:5:12:4'
                      -- and a string separator i.e.':' and irrespective of the order of individual selections within
                      -- the string, returns 'TRUE' if the strings are equal else returns 'FALSE'.
                      -----------------------------------------------------------------------------------------------------------------
                        IS
                          l_table1 apex_application_global.vc_arr2;
                          l_table2 apex_application_global.vc_arr2;
                          v_return varchar2(8);
                        BEGIN
                          if p_string1 is null or p_string2 is null or p_separator is null then
                            raise_application_error(-20001,'USAGE: CheckMultiSelectStrings() function requires 2 not null strings and a separator character' );
                          end if;
                          l_table1 := apex_util.string_to_table(p_string1, p_separator);
                          l_table2 := apex_util.string_to_table(p_string2, p_separator);
                          if l_table1.count() <> l_table2.count() then
                              return 'FALSE';
                          end if;
                          <<OUTER_LOOP>>
                          FOR i IN 1..l_table1.count() LOOP
                              FOR j IN 1..l_table2.count() LOOP
                                 if l_table1(i)=l_table2(j) then
                                   v_return:='TRUE';
                                   continue OUTER_LOOP;
                                 else
                                   v_return:= 'FALSE';
                                   continue;
                                 end if;
                              END LOOP; -- FOR j IN 1..l_table2...
                              EXIT OUTER_LOOP WHEN v_return = 'FALSE';
                          END LOOP; --FOR i IN 1..l_table1.
                          RETURN v_return;
                        END;
                      

                      --============HERE IS THE TEST CODE FOR THE FUNCTION==================

                      set serveroutput on
                      declare
                      v_return varchar2(8);
                      begin
                      v_return:= CheckMultiSelectStringsEqual('1:2:3:4','2:1:3:4',':');
                            if v_return='TRUE' then
                                dbms_output.put_line('Strings are equal');
                            else
                                dbms_output.put_line('Strings are NOT equal');
                            end if;
                      end;
                      

                      Hope this helps someone...

                      PaulP