apex_string.string_to_table giving ORA-00902: invalid datatype

Mr.Peabody

I want to convert colon delimited list into separate rows. I am passing col1 VARCHAR2(4000) to apex_string.string_to_table.

select (select column_value from table(apex_string.string_to_table(col1))) from tab1;

getting ORA-00902: invalid datatype.

Thanks!!!

Hub Tijhuis

But if you have more then 1 row in your tab1 you will get an :ORA-01427: single-row subquery returns more than one row

If you need to proces more then 1 row:

with tab1(col1) as (select 'a;b;c;d' from dual union select 'f;g;h;i' from dual)

select column_value from tab1,table(apex_string.split(col1,';'));

cormaco

Use the SPLIT function instead, this works in Oracle 18XE with APEX 20.1:

with tab1(col1) as (select 'a:b:c:d' from dual)
select column_value as colval from table(apex_string.split((select col1 from tab1),':'));

COLVAL              
--------------------
a
b
c
d

The difference is that SPLIT returns a SQL type and string_to_table returns a PL/SQL type and only SQL types can be used in a SQL TABLE expression.

Hub Tijhuis

But if you have more then 1 row in your tab1 you will get an :ORA-01427: single-row subquery returns more than one row

If you need to proces more then 1 row:

with tab1(col1) as (select 'a;b;c;d' from dual union select 'f;g;h;i' from dual)

select column_value from tab1,table(apex_string.split(col1,';'));

Mr.Peabody

https://community.oracle.com/tech/developers/discussion/comment/16791735#Comment_16791735

What should be done if I have one more colon delimited list column and that also is required to be added in this query?

Any ideas?

cormaco

You can just concatenate the columns:

with tab1(col1,col2) as (select 'a:b:c:d','f:g:h:i' from dual)
select column_value as colval from tab1, table(apex_string.split(col1||':'||col2,':'));

COLVAL              
--------------------
a
b
c
d
f
g
h
i

Mr.Peabody

https://community.oracle.com/tech/developers/discussion/comment/16791738#Comment_16791738

Here, it will concatenate both the column values instead I want both separate with addition primary key column.

PK    COLVAL1   COLVAL2             
------------------------
1      a         a1
2      b         b1 
2      c         b2
2      d         b1
3      f         c2
3      g         c3
3      h         c4
3      i         c5

The values in colon delimited values (COLVAL1 and COLVAL2) are user_ids in my case.

Any suggestions?

cormaco

Can you post the input data that should bring this result, maybe in a with clause?

Mr.Peabody

https://community.oracle.com/tech/developers/discussion/comment/16791751#Comment_16791751

Sorry, I can't post the actual data, but this is something similar to that. Actually, COLVAL1 and COLVAL2 are user ids (but of different categories) in my case which have access to ID (PK).

By using the query, I also need to create a view. Its such a typical scenario which can't be ignored and will have to do it only this way.

ID    COLVAL1     COLVAL2
-------------------------
1     10:20:30     101
2     20           102:103
3     20:30        101:102
4     10:40        103
5     20           101:102:103

Hub Tijhuis

But you don't give any clue on what way the different values of colval1 and colval2 should be combined.

So here is one option that combines the different values in order of column_value

with tab1(pk,colval1,colval2) as

(

select 1    ,'10:20:30'    ,'101'         from dual union

select 2    ,'20'          ,'102:103'     from dual union

select 3    ,'20:30'      ,'101:102'     from dual union

select 4    ,'10:40'       ,'103'         from dual union

select 5    ,'20'          ,'101:102:103' from dual

)

, s1 as

(select rank() over (partition by pk order by column_value ) rn, pk, column_value as col1 from tab1, table(apex_string.split(colval1,':')))

, s2 as

( select rank() over (partition by pk order by column_value ) rn, pk, column_value as col2 from tab1, table(apex_string.split(colval2,':')))

select nvl(s1.pk,s2.pk) pk, col1, col2 , s1.rn from s1 full outer join s2 on s1.pk = s2.pk and s1.rn = s2.rn

order by 1,2,3