How to insert multiple values with popup LOV in Oracle APEX?

0
votes

I want to find a way to insert multiple values in a link table, for that I use popup LOV item, using this item users can choose multiple values. And I want to insert that values to link table.

I tried with

INSERT INTO LINK_TABLE (FK_1, FK_2)
VALUES (:P2_POPUP, :P2_RECORD2);

When I try to insert more than one value, I got ORA-01722: invalid number

1
sqloracleplsqloracle-apex
I'd say that you have to explain it a lot better. What "multiple values"? What does LoV do? Do you allow users to select more than a single value? What data are you actually trying to insert? - Littlefoot
Sorry for bad explanation, users can select more than one value in POPUP LoV. - Goku
I have table which need tags, every record in that table can have more than one tag. For that I need to use link table and POP LoV which can select more than one value - Goku

1 Answers

2
votes

I presume that

  • P2_POPUP contains a single value, while
  • P2_RECORD2 contains one or more values selected by user
    • it means that Apex stores them as colon-separated values, which - furthermore ...
    • ... means that you have to split it into rows

For example: TEMP CTE "simulates" values that P2_POPUP (1) and P2_RECORD2 (10:30:40) contain. Query from line #3 to line #6 creates several rows out of it:

SQL> with temp (p2_popup, p2_record2) as
  2    (select 1, '10:30:40' from dual)
  3  select p2_popup,
  4    regexp_substr(p2_record2, '[^:]+', 1, level) p2_rec
  5  from temp
  6  connect by level <= regexp_count(p2_record2, ':') + 1;

  P2_POPUP P2_REC
---------- --------------------------------
         1 10
         1 30
         1 40

SQL>

It means that your code would look like this:

insert into link_table (fk_1, fk_2)
select :P2_POPUP,
       regexp_substr(:P2_RECORD2, '[^:]+', 1, level) p2_rec
from dual
connect by level <= regexp_count(:P2_RECORD2, ':') + 1;