How show results of split like as table in ORACLE

Question

i need to have a query that simulates a query to a table starting from a split of a string:

String: A-1:B-2:C-3

Im trying some like that, but i cant get the other value:image

SELECT (SELECT y.column_value AS val
           FROM TABLE(apex_string.split(p_str => r.column_value,
                                        p_sep => '-')) y
          WHERE rownum = 1) AS vall,
        (SELECT y.column_value AS val
           FROM TABLE(apex_string.split(p_str => r.column_value,
                                        p_sep => '-')) y
          WHERE rownum = 2) AS valr
   FROM TABLE(apex_string.split(p_str => 'A-1:B-2:C-3', p_sep => ':')) r

I need have a result as a query like the image: example

Answer 1

Similarly, with regular expressions; P7_VALUE has been entered manually and is set to submit when Enter is pressed. In real scenario, I presume that its value will be fetched from the database as result of multi-select Select list or Shuttle item.

Query:

select regexp_substr(column_value, '^\w+') col1,
       regexp_substr(column_value, '\w+$') col2
from table(apex_string.split(:P7_VALUE, ':'))

Result: