CODEWITHSUNDEEP
sqlplsqloracle-apex
TheFlori
TheFlori

Reputation: 69

APEX: How to store values after separating into a array and write them into a table

I've a APEX Item with DATA like Test1:Test2:Test3:Test:4:CAR

I'd like to separate them. After each ":" they should get a own row.

I realized this like this:

SELECT trim(regexp_substr(:P_69, '[^:]+', 1, LEVEL)) str
FROM DUAL
CONNECT BY instr(:P_69, ':', 1, LEVEL - 1) > 0;

Now i get: 

str
Test1
Test2
Test3
Test4
CAR

but now i want to insert Data in a table like insert [... ...] when d.Test1 = Test1

the d.Test1 is from an other table than the information in my Apex ITEM.

Does anyone have a idea how this could be made?

Upvotes: 0

Views: 1148

Answers (1)

Daniel Frech
Daniel Frech

Reputation: 335

You might use APEX_STRING.split for splitting your item like:

SELECT column_value
  FROM TABLE(APEX_STRING.SPLIT(:P_69, ':'))

This you can also use in a join with another table that you should be able to use for your insert:

SELECT column_value
  FROM TABLE(APEX_STRING.SPLIT(:P_69, ':')) i
 INNER JOIN your_other_table d ON d.test1 = i.column_value

Upvotes: 3

Related Questions