CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
Goku
Goku

Reputation: 441

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

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

Upvotes: 0

Views: 2175

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

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;

Upvotes: 2

Related Questions