This discussion is archived
7 Replies Latest reply: Sep 16, 2013 5: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 Explorer
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points