A shopping cart, the rows of a multi-step wizard, or a query result the user reviews before saving: all of these are rows that belong to one user's session rather than to a permanent table. Oracle APEX collections hold exactly that. A collection is a named set of rows tied to an APEX session, stored in tables APEX owns, read through the APEX_COLLECTIONS view, and gone when the session ends.
This guide covers APEX_COLLECTION with tested examples and their real output: creating collections from values and queries, updating and merging members, reordering and deleting them, and using the changed flag and MD5 checksums to tell what the user changed. It also flags a MERGE_MEMBERS requirement in APEX 26.1 that the documentation leaves out.
Quick Reference
| Task | Subprogram |
|---|---|
| Create, check, and count | CREATE_COLLECTION, CREATE_OR_TRUNCATE_COLLECTION, COLLECTION_EXISTS, COLLECTION_MEMBER_COUNT |
| Add members | ADD_MEMBER, ADD_MEMBERS |
| Create from a query | CREATE_COLLECTION_FROM_QUERY, CREATE_COLLECTION_FROM_QUERY2, CREATE_COLLECTION_FROM_QUERY_B, CREATE_COLLECTION_FROM_QUERYB2 |
| Update members | UPDATE_MEMBER, UPDATE_MEMBER_ATTRIBUTE, UPDATE_MEMBERS, MERGE_MEMBERS |
| Detect changes | COLLECTION_HAS_CHANGED, RESET_COLLECTION_CHANGED, RESET_COLLECTION_CHANGED_ALL, GET_MEMBER_MD5 |
| Reorder members | MOVE_MEMBER_UP, MOVE_MEMBER_DOWN, SORT_MEMBERS, RESEQUENCE_COLLECTION |
| Delete members and collections | DELETE_MEMBER, DELETE_MEMBERS, TRUNCATE_COLLECTION, DELETE_COLLECTION, DELETE_ALL_COLLECTIONS, DELETE_ALL_COLLECTIONS_SESSION |
How Collections Are Structured
Each row of a collection, called a member, has a SEQ_ID and a fixed set of attributes: C001 to C050 as VARCHAR2(4000), N001 to N005 for numbers, D001 to D005 for dates, CLOB001, BLOB001, and XMLTYPE001, plus MD5_ORIGINAL, the checksum taken when the member was created. Collection names are not case-sensitive, because APEX converts them to upper case. The APEX_COLLECTIONS view shows only the current session's collections for the current application.
Reading a collection:
select seq_id, c001 as sku, n001 as qty from apex_collections where collection_name = 'CART'
That query is how a report or interactive grid shows a collection on a page. For a first walkthrough of a cart-style use, see the Oracle APEX collection example.
How to Run These Examples
Every APEX_COLLECTION procedure needs an APEX session, which a page process has automatically. In a script, create one first with APEX_SESSION.CREATE_SESSION for application 200, page 1, as shown in the guide to creating APEX sessions and managing session state from PL/SQL. The procedures raise an error when the collection they work on does not exist.
The examples ran in Oracle APEX 26.1, several of them reading the orb_orders and orb_products tables of the Orbit Outfitters sample schema, available from the orb_tables repository on GitHub. Run them as your workspace schema with server output switched on. The output under each example is exactly what the database printed.
Creating and Filling Collections
CREATE_COLLECTION, CREATE_OR_TRUNCATE_COLLECTION, COLLECTION_EXISTS, and COLLECTION_MEMBER_COUNT
CREATE_COLLECTION creates an empty collection and raises an error if it already exists, unless p_truncate_if_exists is 'YES'. CREATE_OR_TRUNCATE_COLLECTION creates it or empties it. COLLECTION_EXISTS tells whether it exists, and COLLECTION_MEMBER_COUNT returns the number of members.
Syntax:
apex_collection.create_collection(p_collection_name in varchar2, p_truncate_if_exists in varchar2 default 'NO') apex_collection.create_or_truncate_collection(p_collection_name in varchar2) apex_collection.collection_exists(p_collection_name in varchar2) return boolean apex_collection.collection_member_count(p_collection_name in varchar2) return number
ADD_MEMBER and ADD_MEMBERS
ADD_MEMBER adds one member with the values of p_c001 to p_c050, p_n001 to p_n005, p_d001 to p_d005, p_clob001, p_blob001, and p_xmltype001; called as a function, it returns the new SEQ_ID. ADD_MEMBERS adds many members from arrays: apex_application_global.vc_arr2 for p_c001 to p_c050, n_arr for numbers, and d_arr for dates. The number of elements in p_c001 decides how many members are added. p_generate_md5 set to 'YES' stores a checksum of each new member.
Syntax:
apex_collection.add_member(p_collection_name in varchar2, p_c001 .. p_c050 in varchar2 default null,
p_n001 .. p_n005 in number default null, p_d001 .. p_d005 in date default null, p_clob001 in clob default empty_clob(),
p_blob001 in blob default empty_blob(), p_xmltype001 in sys.xmltype default null, p_generate_md5 in varchar2 default 'NO')
[return number]
apex_collection.add_members(p_collection_name in varchar2, p_c001 .. p_c050 in apex_application_global.vc_arr2,
p_n001 .. p_n005 in apex_application_global.n_arr, p_d001 .. p_d005 in apex_application_global.d_arr,
p_generate_md5 in varchar2 default 'NO')This example needs a session of application 200, page 1. It runs a PL/SQL block and then queries the collection.
Example:
declare
l_seq number;
l_skus apex_application_global.vc_arr2;
l_qtys apex_application_global.n_arr;
begin
apex_collection.create_or_truncate_collection(p_collection_name => 'CART');
-- one member: c001..c050 strings, n001..n005 numbers, d001..d005 dates
apex_collection.add_member(
p_collection_name => 'CART',
p_c001 => 'TNT-1002',
p_c002 => 'Trailblazer 2-Person Tent',
p_n001 => 1,
p_n002 => 274.99,
p_d001 => date '2026-03-14');
-- the function returns the new member's sequence ID
l_seq := apex_collection.add_member(p_collection_name => 'cart', -- names are not case-sensitive
p_c001 => 'SLP-1002', p_c002 => 'Nightfall 0° Down Bag',
p_n001 => 2, p_n002 => 389.50);
dbms_output.put_line('new member: ' || l_seq);
-- many members at once, one array per attribute
l_skus(1) := 'KIT-1003'; l_qtys(1) := 4;
l_skus(2) := 'KIT-1008'; l_qtys(2) := 6;
apex_collection.add_members(p_collection_name => 'CART', p_c001 => l_skus, p_n001 => l_qtys);
dbms_output.put_line('exists: ' || case when apex_collection.collection_exists('CART') then 'yes' else 'no' end
|| ', members: ' || apex_collection.collection_member_count('CART'));
end;
/
select seq_id, c001, c002, n001, n002, d001
from apex_collections
where collection_name = 'CART'
order by seq_id;Output:
new member: 2
exists: yes, members: 4
SEQ_ID C001 C002 N001 N002 D001
------ -------- ------------------------- ---- ------ -----------
1 TNT-1002 Trailblazer 2-Person Tent 1 274.99 14-MAR-2026
2 SLP-1002 Nightfall 0° Down Bag 2 389.5 (null)
3 KIT-1003 (null) 4 (null) (null)
4 KIT-1008 (null) 6 (null) (null)The second call used the lower-case name cart and still added to CART, because names are converted to upper case.
Collections from Queries
Four procedures create a collection from a query. They raise an error if the collection exists, unless p_truncate_if_exists is 'YES'.
| Procedure | Query columns | Notes |
|---|---|---|
| CREATE_COLLECTION_FROM_QUERY | Up to 50, into C001 onward. | Row by row; p_generate_md5 set to 'YES' stores checksums. |
| CREATE_COLLECTION_FROM_QUERY2 | 5 numbers (N001 onward), 5 dates (D001 onward), then up to 50 into C001 onward. | As above. |
| CREATE_COLLECTION_FROM_QUERY_B | Up to 50, into C001 onward. | Bulk and much faster, but no checksums and values up to 4,000 bytes. p_names and p_values bind variables by name; p_max_row_count limits rows. |
| CREATE_COLLECTION_FROM_QUERYB2 | 5 numbers, 5 dates, then up to 50. | Bulk, as with _B. |
Syntax:
apex_collection.create_collection_from_query[2](p_collection_name in varchar2, p_query in varchar2,
p_generate_md5 in varchar2 default 'NO', p_truncate_if_exists in varchar2 default 'NO')
apex_collection.create_collection_from_query_b | create_collection_from_queryb2(p_collection_name in varchar2, p_query in varchar2,
[p_names in apex_application_global.vc_arr2, p_values in apex_application_global.vc_arr2,]
p_max_row_count in number default null, p_truncate_if_exists in varchar2 default 'NO')The query is parsed as the application's parsing schema. For anything based on user input, use _B with bind variables rather than concatenating values into the query text, which would open the door to SQL injection.
This example needs a session of application 200, page 1.
Example:
declare
l_names apex_application_global.vc_arr2;
l_vals apex_application_global.vc_arr2;
begin
-- columns go to c001, c002, ... in order
apex_collection.create_collection_from_query(
p_collection_name => 'ORDERS',
p_query => q'~select order_number, status from orb_orders
where status = 'SHIPPED' order by order_id~',
p_generate_md5 => 'YES');
-- ...2: five numbers (n001-n005), five dates (d001-d005), then strings (c001...)
apex_collection.create_collection_from_query2(
p_collection_name => 'ORDER_TOTALS',
p_query => q'~select order_id, order_total, null, null, null,
order_date, null, null, null, null, order_number
from orb_orders where status = 'SHIPPED' order by order_id~');
-- _b: bulk fetch, faster, no MD5; binds by name
l_names(1) := 'STATUS'; l_vals(1) := 'NEW';
apex_collection.create_collection_from_query_b(
p_collection_name => 'NEW_ORDERS',
p_query => 'select order_number, channel from orb_orders where status = :status',
p_names => l_names,
p_values => l_vals,
p_max_row_count => 5);
-- b2: bulk with numbers and dates first; p_truncate_if_exists replaces an existing one
apex_collection.create_collection_from_queryb2(
p_collection_name => 'ORDER_TOTALS',
p_query => q'~select order_id, order_total, null, null, null,
order_date, null, null, null, null, order_number
from orb_orders where status = 'DELIVERED'~',
p_truncate_if_exists => 'YES');
end;
/
select collection_name, count(*) as members, min(c001) as first_c001,
max(n002) as max_n002, count(md5_original) as with_md5
from apex_collections
group by collection_name
order by collection_name;Output:
COLLECTION_NAME MEMBERS FIRST_C001 MAX_N002 WITH_MD5 --------------- ------- ---------- -------- -------- NEW_ORDERS 2 ORD-12272 (null) 0 ORDERS 32 ORD-12206 (null) 32 ORDER_TOTALS 2093 ORD-10002 49117.4 0
Only the row-by-row ORDERS collection has checksums, and ORDER_TOTALS holds the delivered orders because the last call replaced it with p_truncate_if_exists. For thousands of rows, the bulk versions are the practical choice.
Changing Members
UPDATE_MEMBER and UPDATE_MEMBER_ATTRIBUTE
UPDATE_MEMBER replaces all of a member's attributes, so any you do not pass become null. UPDATE_MEMBER_ATTRIBUTE sets one attribute and keeps the others. Its six overloads take the attribute's number and a value of its type: p_attr_number with p_attr_value for C001 to C050, p_number_value for N001 to N005, or p_date_value for D001 to D005; or p_clob_number, p_blob_number, or p_xmltype_number (always 1) with their values.
Syntax:
apex_collection.update_member(p_collection_name in varchar2, p_seq in number, p_c001 .. p_c050 in varchar2 default null,
p_n001 .. p_n005 in number default null, p_d001 .. p_d005 in date default null, p_clob001 in clob default empty_clob(),
p_blob001 in blob default empty_blob(), p_xmltype001 in sys.xmltype default null)
apex_collection.update_member_attribute(p_collection_name in varchar2, p_seq in number,
p_attr_number in number, p_attr_value in varchar2 | p_number_value in number | p_date_value in date)
apex_collection.update_member_attribute(p_collection_name in varchar2, p_seq in number,
p_clob_number in number, p_clob_value in clob | p_blob_number .. | p_xmltype_number ..)COLLECTION_HAS_CHANGED, RESET_COLLECTION_CHANGED, RESET_COLLECTION_CHANGED_ALL, and GET_MEMBER_MD5
Every collection has a changed flag that any change to its members sets. COLLECTION_HAS_CHANGED reads it; RESET_COLLECTION_CHANGED clears it for one collection and RESET_COLLECTION_CHANGED_ALL for all of the session's collections. Call a reset right after loading, so the flag tells you whether the user changed anything. GET_MEMBER_MD5 computes a member's checksum now; compare it with MD5_ORIGINAL to find the members that changed since they were created with a checksum.
Syntax:
apex_collection.collection_has_changed(p_collection_name in varchar2) return boolean apex_collection.reset_collection_changed(p_collection_name in varchar2) apex_collection.reset_collection_changed_all apex_collection.get_member_md5(p_collection_name in varchar2, p_seq in number) return varchar2
This example needs a session of application 200, page 1.
Example:
begin
apex_collection.create_collection_from_query(
p_collection_name => 'CART',
p_query => q'~select sku, product_name, 1 from orb_products
where sku in ('TNT-1002', 'SLP-1002', 'KIT-1003') order by sku~',
p_generate_md5 => 'YES');
apex_collection.reset_collection_changed('CART');
-- replaces all attributes of member 1: the ones not passed become null
apex_collection.update_member(p_collection_name => 'CART', p_seq => 1,
p_c001 => 'KIT-1003', p_c002 => 'Titanium Pot 750 ml', p_n001 => 3);
-- sets a single attribute, keeping the others
apex_collection.update_member_attribute(p_collection_name => 'CART', p_seq => 2,
p_attr_number => 3, p_attr_value => '2');
apex_collection.update_member_attribute(p_collection_name => 'CART', p_seq => 2,
p_attr_number => 1, p_number_value => 5);
apex_collection.update_member_attribute(p_collection_name => 'CART', p_seq => 2,
p_attr_number => 1, p_date_value => date '2026-04-01');
apex_collection.update_member_attribute(p_collection_name => 'CART', p_seq => 2,
p_clob_number => 1, p_clob_value => 'Gift wrap, please.');
dbms_output.put_line('changed: ' ||
case when apex_collection.collection_has_changed('CART') then 'yes' else 'no' end);
end;
/
select seq_id, c001, c002, c003, n001, d001, dbms_lob.substr(clob001, 30, 1) as clob001,
case when md5_original = apex_collection.get_member_md5('CART', seq_id)
then 'same' else 'changed' end as md5
from apex_collections
where collection_name = 'CART'
order by seq_id;Output:
changed: yes
SEQ_ID C001 C002 C003 N001 D001 CLOB001 MD5
------ -------- ------------------------- ------ ------ ----------- ------------------ -------
1 KIT-1003 Titanium Pot 750 ml (null) 3 (null) (null) changed
2 SLP-1002 Nightfall 0° Down Bag 2 5 01-APR-2026 Gift wrap, please. changed
3 TNT-1002 Trailblazer 2-Person Tent 1 (null) (null) (null) sameMember 1 lost its C003 value because UPDATE_MEMBER replaced every attribute, while member 2 kept its SKU and name because UPDATE_MEMBER_ATTRIBUTE changed one attribute at a time. The MD5 comparison pinpointed exactly which members changed, which is how a save process can write back only what the user edited.
UPDATE_MEMBERS and MERGE_MEMBERS
Both take arrays, the way a tabular form posts them. UPDATE_MEMBERS updates the members whose sequence IDs are in p_seq, replacing all their attributes as UPDATE_MEMBER does, and raises an error for an ID that does not exist.
MERGE_MEMBERS makes the collection match the arrays: members whose SEQ_ID is in p_seq are updated, members not in p_seq are deleted, and array rows with a new sequence ID are added under that ID. Rows whose attribute number p_null_index equals p_null_value count as empty and are dropped, for example a quantity of 0. If the collection does not exist, it is created, from p_init_query if given. In 26.1, p_seq is effectively required: without it the merge raises ORA-01403, and rows with a null sequence ID are ignored.
Syntax:
apex_collection.update_members(p_collection_name in varchar2, p_seq in apex_application_global.vc_arr2,
p_c001 .. p_c050 in apex_application_global.vc_arr2, p_n001 .. p_n005 in apex_application_global.n_arr,
p_d001 .. p_d005 in apex_application_global.d_arr)
apex_collection.merge_members(p_collection_name in varchar2, p_seq in apex_application_global.vc_arr2,
p_c001 .. p_c050 in apex_application_global.vc_arr2, p_null_index in number default 1,
p_null_value in varchar2 default null, p_init_query in varchar2 default null)This example needs a session of application 200, page 1. It runs an update, queries, runs a merge, and queries again.
Example:
declare
l_seq apex_application_global.vc_arr2;
l_sku apex_application_global.vc_arr2;
l_qty apex_application_global.vc_arr2;
begin
apex_collection.create_collection_from_query(
p_collection_name => 'CART',
p_query => q'~select sku, '1' from orb_products
where sku in ('TNT-1002', 'SLP-1002', 'KIT-1003') order by sku~');
-- update members 1 and 3 from arrays, as a tabular form would post them
l_seq(1) := 1; l_sku(1) := 'KIT-1003'; l_qty(1) := '4';
l_seq(2) := 3; l_sku(2) := 'TNT-1002'; l_qty(2) := '2';
apex_collection.update_members(p_collection_name => 'CART', p_seq => l_seq,
p_c001 => l_sku, p_c002 => l_qty);
end;
/
select seq_id, c001, c002 from apex_collections where collection_name = 'CART' order by seq_id;
declare
l_seq apex_application_global.vc_arr2;
l_sku apex_application_global.vc_arr2;
l_qty apex_application_global.vc_arr2;
begin
-- merge: member 1 updated, 2 removed (its quantity c002 is the null value 0),
-- 3 deleted (not in the arrays), and 4 added (a new sequence ID)
l_seq(1) := 1; l_sku(1) := 'KIT-1003'; l_qty(1) := '5';
l_seq(2) := 2; l_sku(2) := 'SLP-1002'; l_qty(2) := '0';
l_seq(3) := 4; l_sku(3) := 'KIT-1008'; l_qty(3) := '6';
apex_collection.merge_members(p_collection_name => 'CART', p_seq => l_seq,
p_c001 => l_sku, p_c002 => l_qty,
p_null_index => 2, p_null_value => '0');
end;
/
select seq_id, c001, c002 from apex_collections where collection_name = 'CART' order by seq_id;Output:
SEQ_ID C001 C002
------ -------- ----
1 KIT-1003 4
2 SLP-1002 1
3 TNT-1002 2
SEQ_ID C001 C002
------ -------- ----
1 KIT-1003 5
4 KIT-1008 6After the merge, member 1 was updated, member 2 was dropped because its quantity was the null value 0, member 3 was deleted because it was missing from the arrays, and member 4 was added.
Ordering and Deleting Members
MOVE_MEMBER_UP, MOVE_MEMBER_DOWN, SORT_MEMBERS, and RESEQUENCE_COLLECTION
MOVE_MEMBER_UP swaps a member with the next one, so its SEQ_ID goes up by one, and MOVE_MEMBER_DOWN swaps it with the previous one. At the ends they do nothing. SORT_MEMBERS sorts the members by the character attribute numbered p_sort_on_column_number and numbers them from 1. RESEQUENCE_COLLECTION numbers the members from 1 without gaps, keeping their order.
Syntax:
apex_collection.move_member_up | move_member_down(p_collection_name in varchar2, p_seq in number) apex_collection.sort_members(p_collection_name in varchar2, p_sort_on_column_number in number) apex_collection.resequence_collection(p_collection_name in varchar2)
DELETE_MEMBER and DELETE_MEMBERS
DELETE_MEMBER deletes the member with a given sequence ID, leaving a gap in the numbering. DELETE_MEMBERS deletes every member whose character attribute p_attr_number equals p_attr_value, or is null when the value is null.
Syntax:
apex_collection.delete_member(p_collection_name in varchar2, p_seq in number) apex_collection.delete_members(p_collection_name in varchar2, p_attr_number in number, p_attr_value in varchar2)
This example needs a session of application 200, page 1.
Example:
declare
procedure show(p_label varchar2) is
l_list varchar2(400);
begin
select listagg(seq_id || ':' || c001, ' ') within group (order by seq_id) into l_list
from apex_collections where collection_name = 'CART';
dbms_output.put_line(rpad(p_label, 16) || l_list);
end;
begin
apex_collection.create_collection_from_query(
p_collection_name => 'CART',
p_query => q'~select sku from orb_products
where sku in ('TNT-1002', 'SLP-1002', 'KIT-1003', 'KIT-1008', 'FUR-1001')
order by product_id~');
show('created');
apex_collection.move_member_up('CART', p_seq => 3); show('3 up (+1)');
apex_collection.move_member_down('CART', p_seq => 3); show('3 down (-1)');
apex_collection.sort_members('CART', p_sort_on_column_number => 1); show('sorted by c001');
apex_collection.delete_member('CART', p_seq => 2); show('2 deleted');
apex_collection.resequence_collection('CART'); show('resequenced');
apex_collection.delete_members('CART', p_attr_number => 1, p_attr_value => 'TNT-1002');
show('TNT deleted');
end;
/Output:
created 1:TNT-1002 2:SLP-1002 3:KIT-1003 4:KIT-1008 5:FUR-1001 3 up (+1) 1:TNT-1002 2:SLP-1002 3:KIT-1008 4:KIT-1003 5:FUR-1001 3 down (-1) 1:TNT-1002 2:KIT-1008 3:SLP-1002 4:KIT-1003 5:FUR-1001 sorted by c001 1:FUR-1001 2:KIT-1003 3:KIT-1008 4:SLP-1002 5:TNT-1002 2 deleted 1:FUR-1001 3:KIT-1008 4:SLP-1002 5:TNT-1002 resequenced 1:FUR-1001 2:KIT-1008 3:SLP-1002 4:TNT-1002 TNT deleted 1:FUR-1001 2:KIT-1008 3:SLP-1002
"Up" means the SEQ_ID goes up, which moves the member later in the list, the opposite of what many people expect from a list shown top to bottom. Wire your Move Up and Move Down buttons accordingly.
TRUNCATE_COLLECTION, DELETE_COLLECTION, DELETE_ALL_COLLECTIONS, and DELETE_ALL_COLLECTIONS_SESSION
TRUNCATE_COLLECTION deletes all members and keeps the collection; DELETE_COLLECTION deletes the collection itself. DELETE_ALL_COLLECTIONS deletes every collection of the current application in the session, and DELETE_ALL_COLLECTIONS_SESSION those of every application in the session.
Syntax:
apex_collection.truncate_collection | delete_collection(p_collection_name in varchar2) apex_collection.delete_all_collections apex_collection.delete_all_collections_session
This example needs a session of application 200, page 1. Creating the same collection twice fails on purpose at the end.
Example:
declare
procedure list(p_label varchar2) is
l_list varchar2(400);
begin
select listagg(n, ' ') within group (order by n) into l_list
from (select column_value || '(' || apex_collection.collection_member_count(column_value) || ')' as n
from table(apex_t_varchar2('A', 'B', 'C'))
where apex_collection.collection_exists(column_value));
dbms_output.put_line(rpad(p_label, 20) || nvl(l_list, '-'));
end;
begin
for c in (select column_value as name from table(apex_t_varchar2('A', 'B', 'C'))) loop
apex_collection.create_collection(c.name);
apex_collection.add_member(c.name, p_c001 => 'x');
end loop;
list('created');
apex_collection.truncate_collection('A'); list('A truncated');
apex_collection.delete_collection('B'); list('B deleted');
apex_collection.create_collection('C', p_truncate_if_exists => 'YES');
list('C re-created');
apex_collection.delete_all_collections; list('all deleted');
begin
apex_collection.create_collection('A');
apex_collection.create_collection('A');
exception when others then
dbms_output.put_line('twice: ' || regexp_replace(sqlerrm, '^ORA-\d+: '));
end;
apex_collection.delete_all_collections_session;
apex_collection.reset_collection_changed_all;
end;
/Output:
created A(1) B(1) C(1) A truncated A(0) B(1) C(1) B deleted A(0) C(1) C re-created A(0) C(0) all deleted - twice: Application collection exists
Collections disappear on their own when the session ends, but deleting them after a wizard completes keeps a long session tidy and avoids stale data if the user starts again. Where collections fit among processes is covered in the guide to page processes in Oracle APEX.
Conclusion
APEX_COLLECTION keeps rows in session state for carts, wizards, and review-before-save pages. Create a collection empty or from a query, row by row with checksums or in bulk with bind variables; add members from values or arrays; update whole members or single attributes; merge tabular-form arrays in one call, remembering that 26.1 needs p_seq; reorder and delete members; and use the changed flag and MD5 checksums to find exactly what the user edited. Read everything through the APEX_COLLECTIONS view, and use the bulk procedures with binds whenever the query depends on user input.
