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;
VANJ wrote: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:
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?
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:
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;
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.
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;