5 Replies Latest reply: Feb 14, 2013 6:02 PM by VANJ RSS

    Shuttle item - Saving changes

    VANJ
      The built-in shuttle item is a good way to visualize and change multi-valued data. If the data is coming from a single row & column (delimted), using the builtin Row Fetch and Automatic DML processes to read/write it work well.

      But what if the data is coming from multiple rows in a source table? Say 10 rows are selected (right hand of shuttle) out of a LOV of 50. The Source for the Shuttle item simply has the query to retrieve the 10 rows and the APEX engine automatically converts the multiple values into 1 delimited value needed by the shuttle.

      But when it comes to saving the changes to the shuttle, there is no easy way to identify what changed. Say I de-select 1 and select 2 new items from the left side of shuttle. The item's value in session state is 1 colon-delimited string containing the currently selected values. How is this translated into the 1 DELETE and 2 INSERTs needed against the base table?

      I can think of saving the before-subimit value into a hidden item, comparing it to the after-submit value and stuff like that but that seems like a lot of work. How is this sort of thing done efficiently?

      Thanks
        • 1. Re: Shuttle item - Saving changes
          VANJ
          Bump. Thanks.
          • 2. Re: Shuttle item - Saving changes
            jrimblas
            Probably not the answer you're looking for, but whenever possible, I simply delete the previous rows and re-insert the newly selected values from the shuttle. It's easy and quick.
            But I agree that when this is not possible it takes a lot of code to find the delta correctly.

            Thanks
            -Jorge
            • 3. Re: Shuttle item - Saving changes
              912368
              Well, to get the seperated entries of the string you can use the built-in APEX-function "STRING_TO_TABLE": http://ioug.itconvergence.com/i/doc/api073.htm

              With simple PL/SQL you can check which entries were deleted and which were inserted.
              You can get the "old" data of your table with cursor and check with the entries of the shuttle.

              It's something like you said with a check after submit. I would do it like this:

              Example (untested):
              DECLARE
              
                v_result    VARCHAR2(4000 CHAR);
                v_check     PLS_INTEGER := 0;
                
                l_shuttle   APEX_APPLICATION_GLOBAL.VC_ARR2;
                
                ----
                CURSOR c_emps IS
                SELECT *
                  FROM emp;
                 WHERE id = 1;
                
              BEGIN
              
                -- set shuttle data to array
                l_shuttle := APEX_UTIL.STRING_TO_TABLE(:P1_SHUTTLE_ITEM);
                
                -- check all table entries (check if something's new)
                
                v_result := '<b>Inserted: </b><br>';
                
                FOR i IN 1 .. l_shuttle.COUNT
                LOOP
                
                  v_check := 1;
                
                  FOR r_emp IN c_emps
                  LOOP
                
                    IF r_emp.id = l_shuttle(i)
                    THEN
                      v_check := 0;
                    END IF;  
                  END LOOP;
                  
                  IF v_check = 1
                  THEN
                  
                    v_result := v_result || r_emp.name || '<br>';
                  END IF;
                END LOOP;
              
                -- check all table entries (check if something's deleted)
                
                v_result := '<b>Deleted: </b><br>';
                
                FOR r_emp IN c_emps
                LOOP
                
                  v_check := 1;
                  
                  -- check all shuttle entries
                  FOR i IN 1 .. l_shuttle.COUNT
                  LOOP
                  
                    -- if both 
                    IF l_shuttle(i) = r_emp.id
                    THEN
                      v_check := 0;        
                    END IF;
                  END LOOP;
                  
                  IF v_check = 1
                  THEN
                  
                    v_result := v_result || r_emp.name || '<br>';
                  END IF;
                END LOOP;
              END;
              • 4. Re: Shuttle item - Saving changes
                fac586
                VANJ wrote:

                But when it comes to saving the changes to the shuttle, there is no easy way to identify what changed. Say I de-select 1 and select 2 new items from the left side of shuttle. The item's value in session state is 1 colon-delimited string containing the currently selected values. How is this translated into the 1 DELETE and 2 INSERTs needed against the base table?

                I can think of saving the before-subimit value into a hidden item, comparing it to the after-submit value and stuff like that but that seems like a lot of work. How is this sort of thing done efficiently?
                Provided the table being modified with the shuttle values contains a column other than the PK and the column updated from the shuttle, this can all be handled using a single DML <tt>MERGE</tt> statement and a reusable utility function:
                begin
                
                  merge into shuttled_states ss
                  using (
                  /*
                    The merge source requires a set of matched pairs: the selected shuttle values
                    against the corresponding values in relevant DB rows, for example:
                    
                    shuttle_st  db_st
                    ----------  -----
                    X           X       -- X is in both shuttle selection and DB rowset
                                Y       -- Y is not in the shuttle selection but is in DB rowset 
                    Z                   -- Z is in the shuttle selection but not DB rowset
                    
                    We therefore need to leave the X row in the DB unchanged, delete the Y row,
                    and insert the Z row.
                    
                    This query should also contain other columns and values required in matching
                    rows in the merge, or in inserted rows.
                  */
                    select
                        shuttle.column_value shuttle_st
                      , db.st db_st
                    from
                        table(string_to_coll(:p37_states)) shuttle
                          full outer join shuttled_states db
                            on shuttle.column_value = db.st) t
                  /*
                    Match conditions normally include matching PK and/or other values to get the
                    relevant DB rowset, but the main requirement here is a predicate that matches
                    using the DB value column from the shuttle/DB rowset pair.
                  */
                  on (ss.st = t.db_st)
                  when matched
                  then
                  /*
                    This is where the restriction applies. Although we don't actually want to
                    update any matched rows as they are unaffected by the shuttle selection, the
                    MERGE can only delete rows that have been updated. However we can't update
                    any of the columns used in the match, so there needs to be at least one
                    column available that can be set to the same value to mark the row as
                    updated...
                  */
                    update
                    set
                        ss.state_name = ss.state_name
                  /*
                    ...which makes it possible to delete rows that are not selected in the shuttle.
                  */
                    delete
                    where
                        t.shuttle_st is null
                  /*
                    Where there's no match the value is newly selected in the shuttle and is
                    inserted as a new row.
                    
                    (Other values required should be included in the query, or handled in a
                    trigger.)
                  */
                  when not matched 
                  then
                    insert
                      (st)
                    values
                      (t.shuttle_st);
                
                end;
                This method relies on turning the selected shuttle values from a colon-delimited string into a SQL rowset. The Oracle-provided <tt>apex_util.string_to_table</tt> function returns a PL/SQL associative array which is not suitable for this. While it is possible to convert a string into rows purely using SQL, these methods are rather obscure and can only be reused by cutting-and-pasting. A simple function that returns a standard SQL collection type can be used as a rowset in a query via the SQL <tt>TABLE</tt> operator, and is highly reusable:
                create or replace function string_to_coll (p_string 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);
                
                  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 pre-defined <tt>sys.ODCIVarchar2List</tt> collection type. In real life you'd create your own, and of course the function would be in a package, not standalone.
                • 5. Re: Shuttle item - Saving changes
                  VANJ
                  Perfect! MERGE and a full outer join was exactly what how I was thinking this would work, too lazy to put the pieces together. As always, thanks for the guidance, Paul.