oracle APEX: how to update table values based on a comma-separated list of IDs from an input field

here’s a quick and dirty APEX tutorial on how to update values of one column for multiple rows in a database table, based on a list of row IDs that a user can conveniently type into a text field.
a bit more detail: i have an Oracle APEX database application with several thousand rows/entries. users need to select some of these rows by changing one column’s value from 0 to 1, to use them in the next step of their process. all rows of the database table have a unique ID (column “ID”), a column that specifies whether the row should be used in the next step (column “column1”), and many further columns for lots of other information.

traditionally, users had to move through the entire table and activate a checkbox next to all desired rows (e.g. IDs 5, 103, 807, 9639, 21485), which involved a lot of scrolling and clicking.
the new way, outlined below, allows users to just type the desired IDs (separated by commas; e.g. “5, 103, 807, 9639, 21485”) into an input field and click a submit-/apply-button, to change all rows to column1 = 1.
(the next step starts with an SQL statement that selects only rows where column1 = 1, to provide more refined output and editing options.)

the procedure assumes the following database table layout for table1:

in your APEX application:

• note the page ID that should get the input field. in our case, it’s page 99 (be sure to change the ID in all following steps & code).
• create a page item of type “text field” and name it “P99_text1”
• create a button named “apply_my_updates”
• create a process named “set_column1” of type “PL/SQL anonymous block”, process point “on submit – after computations and validations”, with condition “when button pressed”: “apply_my_updates”, and the following process source:
UPDATE table1 SET column1 = 1 WHERE id IN (
SELECT regexp_substr(:P99_text1,'[^,]+', 1, level) FROM DUAL
CONNECT BY regexp_substr(:P99_text1, '[^,]+', 1, level) IS NOT NULL
);

when you run your page, enter several IDs separated by commas (e.g. the string “5, 103, 807, 9639, 21485”; with or without spaces) and press the button “apply_my_updates”.
the page process “set_column1” will first split the entered string into a list of IDs using commas as a separator (line 2 in the code), then drop any empty IDs (line 3 in the code; e.g. if there are two commas with no text in between), and then update the rows containing your valid IDs to set column1 = 1 (line 1 in the code).

the code was tested and is working in Oracle Application Express 4.2.4.00.08.
if this is what you were looking for, please let me know if it’s also working for you.