Forum Stats

  • 3,768,996 Users
  • 2,252,894 Discussions
  • 7,874,831 Comments

Discussions

apex_string.string_to_table giving ORA-00902: invalid datatype

Mr.Peabody
Mr.Peabody Member Posts: 11 Red Ribbon
edited Apr 10, 2021 4:42AM in SQL & PL/SQL

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!!!

Best Answer

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 125 Silver Badge
    Accepted Answer

    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,';'));

Answers

  • cormaco
    cormaco Member Posts: 1,722 Bronze Crown

    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
    Hub Tijhuis Member Posts: 125 Silver Badge
    Accepted Answer

    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
    Mr.Peabody Member Posts: 11 Red Ribbon

    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
    cormaco Member Posts: 1,722 Bronze Crown

    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
    Mr.Peabody Member Posts: 11 Red Ribbon

    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
    cormaco Member Posts: 1,722 Bronze Crown

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

  • Mr.Peabody
    Mr.Peabody Member Posts: 11 Red Ribbon
    edited Apr 11, 2021 2:59PM


    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
    Hub Tijhuis Member Posts: 125 Silver Badge

    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