APEX

I have a DB table with a field that stores values that may be colon separated as resulting from a checkbox page item.

1:2
3:4
5:6

My ultimate goal is to turn those values into a table of singular values.

1
2
3
4
5
6

I've gotten close, but can't figure out how to finish the last step. My first step was to create a single query that concatenated all values into one long string. I am able to get

1:2:3:4:5:6

as a result of using the LISTAGG function in the query. My next step was to use a function, STRTAB, that I sourced from this forum to convert the colon delimited string into a result set using the query:

select * from table( strtab( '1:2:3:4:5:6' ))

where the string was derived from the LISTAGG query. The query works when I pass the above string, but if I try to insert the actual LISTAGG query it fails with missing expression. My actual query looks like this:

select * from table( strtab(
select listagg( RESTRICTED, ':' ) WITHIN GROUP ( order by RESTRICTED ) as ID
from SZ_LINKED_PROFILES
where RESTRICTED is not null
and PROFILE in ( select ID
from SZ_PROFILES
where ACCOUNT = :APP_ACCOUNT )))

Anyone know how I can complete the last step? I know I can do this with pl/sql, but I need this to be a single query as I'm using it as the criteria in an IN expression of a larger query.

Thanks in advance for your help!

-Jeff