execute a SQL or PL/SQL code after html insert action in APEX

i have a region in apex that contains a list of options to select

htp.p('
<form>
<select multiple width="100%" style="width: 100%; height: 80%;">   
  ');
for a in 
(select id_plan, year_plan from plan)
loop
  htp.p('
<option value="'||htf.escape_sc(a.id_plan)||'">'||htf.escape_sc(a.year_plan)||'</option> 
end loop;
htp.p('</select>
<input type="submit" value="Submit">
</form>
');

how can i get back the select value(s) and execute an SQL insert action for each one

Answer

As you use Apex, why don’t you use its Select List item (and allow multiple choices)?

Doing so, values you select would be colon-separated (e.g. 10:20:30).

In order to insert those values into a table, a simple option is to create a button and a process which fires when that button is pressed. As button submits the page, selected value(s) are stored into session state and process sees them.

If that select list item’s name is P1_LIST, process would be e.g.

insert into some_table (col) 
select regexp_substr(:P1_LIST, '[^:]+', 1, level)
  from dual
  connect by level <= regexp_count(:P1_LIST, ':') + 1