This discussion is archived
5 Replies Latest reply: Feb 14, 2013 4:02 PM by VANJ RSS

Shuttle item - Saving changes

VANJ Journeyer
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    Bump. Thanks.
  • 2. Re: Shuttle item - Saving changes
    jrimblas Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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.

Legend

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