This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Dec 17, 2012 1:08 AM by chris227 RSS

ORA-01489: result of string concatenation is too long

976910 Newbie
Currently Being Moderated
thanks to Alberto Faenza ,
Who help me in this code but now a face another problem.
I face this problem when I execute the following code
INSERT INTO b (id, gen_value, cnt)
WITH got_values AS
(
   SELECT id, TRIM((column_value).getstringval()) strorig
        , REGEXP_SUBSTR(TRIM((column_value).getstringval()),'\S+$') str
     FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"')) 
)
, got_sdx_cnt AS
(
   SELECT id, strorig
        , COUNT(*) OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig)) cnt
        , ROW_NUMBER() OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig) 
                                 ORDER BY LENGTH(str) DESC, ROWNUM) rn
     FROM got_values
)
SELECT id, strorig, cnt 
  FROM got_sdx_cnt
 WHERE rn=1;
I face this error :
INSERT INTO b (id, gen_value, cnt)
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
  • 1. Re: ORA-01489: result of string concatenation is too long
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    you need to change the type on <tt>gen_value</tt> to CLOB .
    the reason is that varchar2 can only hold 4000 bytes. (This matters if you want to store unicode).

    bye
    TPD

    Edited by: TPD Opitz-Consulting com on 13.12.2012 12:49
  • 2. Re: ORA-01489: result of string concatenation is too long
    chris227 Guru
    Currently Being Moderated
    I guess you was given a solution already in the origin thread which probably doesnt lead to this problem.
  • 3. Re: ORA-01489: result of string concatenation is too long
    976910 Newbie
    Currently Being Moderated
    Yes, Dear Chris,

    but i face problem with that solution when i focus to used insert statement and count numbers and dates .

    count based on soundex function

    my regards to you
  • 4. Re: ORA-01489: result of string concatenation is too long
    odie_63 Guru
    Currently Being Moderated
    What's the database version? (SELECT * FROM v$version)

    What's the max length of GEN_VALUE?

    Dynamic XQuery is unscalable, this shouldn't be used, and anyway doesn't work on the latest versions :
    xmltable(('"'||REPLACE(gen_value,',','","')||'"'))
  • 5. Re: ORA-01489: result of string concatenation is too long
    chris227 Guru
    Currently Being Moderated
    Ok, i am not in yet anymore, but it is easy to alter the select into an insert:
    just add
    insert into b(id, gen_value, cnt) 
    at the beginning

    If there is some refinement on the select necessary, which you cant do by yourself, feel free to ask.
    insert into b(id, gen_value, cnt) 
    
    with data as (
     select
      id
     ,gen_value||',' gen_value
     from a
    )
    ,r  (id, gen_value, l) as (
     select  
      id
     ,substr(gen_value, instr(gen_value, ',') + 1)
     ,substr(
       trim(substr(gen_value,1, instr(gen_value, ',') - 1))
      ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
     )
    from data
    union all
     select  
      id
     ,substr(gen_value, instr(gen_value, ',') + 1)
     ,substr(
       trim(substr(gen_value,1, instr(gen_value, ',') - 1))
      ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
     )
    from r
    where
    length(gen_value) > 0
    )
    , cnts as ( 
    select 
    soundex(l) s
    ,count(*) cnt
    ,max(l) keep ( dense_rank last order by length(l), l desc nulls first) v
    ,id
    ,gen_value w
    from r
    group by
     soundex(l), id, gen_value
    )
     
    select
     id
    ,w
    ,cnt
    --,v
    --,s
    from cnts
    
    select * from b
    
    ID GEN_VALUE CNT 
    3 -  1 
    1 Egypt,BDE AAC AFC,1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010, 1 
    1 BDE AAC AFC,1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010, 1 
    2 -  1 
    1 Egpt,1-1-2010, 1 
    3 JHU KK MNK, 1 
    1 88,Egypt,BDE AAC AFC,1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010, 1 
    2 omar Ahmed Majid,BBA BBG BCEV,ALI Majid, GACD MNF BCV, 1 
    2 GACD MNF BCV, 1 
    1 BCF AAE,Egypt,Egpt,1-1-2010, 1 
    2 BBA BBG BCEV,ALI Majid, GACD MNF BCV, 1 
    2 ALI Majid, GACD MNF BCV, 1 
    1 20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010, 1 
    1 AFD BCF, 88,Egypt,BDE AAC AFC,1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010, 1 
    1 Egypt,Egpt,1-1-2010, 1 
    1 1-1-2010, 1 
    3 BBG HUH ABCE, JHU KK MNK, 1 
    1 1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010, 1 
    1 -  1 
  • 6. Re: ORA-01489: result of string concatenation is too long
    976910 Newbie
    Currently Being Moderated
    Hi, Chris277,
    i tried the SQL on the follwing data not the results not as i need.

    the grouping should be on V based on soundex of words

    drop table a;
    CREATE TABLE  a(id NUMBER(9), gen_value VARCHAR2(200));
     
    INSERT INTO a VALUES(1, 'ACD BCD AAA, AFD BCF, 88,Egypt,BDE AAC AFC,1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010');
    INSERT INTO a VALUES(2, 'AVF BCD,omar Ahmed Majid,BBA BBG BCEV,ALI Majid, GACD MNF BCV');
    INSERT INTO a VALUES(3, 'AFC ABC, BBG HUH ABCE, JHU KK MNK');
    COMMIT;
     drop table b;
    CREATE TABLE  b(id NUMBER(9), Gen_Value VARCHAR2(200), cnt NUMBER(9));
     
    i used that code you gave it but the results not as i want
    insert into b(id, gen_value, cnt) 
     with data as (
     select
      id
     ,gen_value||',' gen_value
     from a
    )
    ,r  (id, gen_value, l) as (
     select  
      id
     ,substr(gen_value, instr(gen_value, ',') + 1)
     ,substr(
       trim(substr(gen_value,1, instr(gen_value, ',') - 1))
      ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
     )
    from data
    union all
     select  
      id
     ,substr(gen_value, instr(gen_value, ',') + 1)
     ,substr(
       trim(substr(gen_value,1, instr(gen_value, ',') - 1))
      ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
     )
    from r
    where
    length(gen_value) > 0
    )
    , cnts as ( 
    select 
    soundex(l) s
    ,count(*) cnt
    ,max(l) keep ( dense_rank last order by length(l), l desc nulls first) v
    ,id
    ,gen_value w
    from r
    group by
     soundex(l), id, gen_value
    )
     select
     id
    ,w
    ,cnt
    --,v
    --,s
    from cnts
    
     
    expected results as following:
      ID GEN_VALUE                   CNT
    ---------- -------------------- ----------
             1 1-1-2010                      2
             1 20-11-2010                    1
             1 88                            1
             1 ACD BCD AAA                   2
             1 BDE AAC AFC                   1
             1 AFD BCF                       1
             1 Egypt                         3
             2 BBA BBG BCEV                  2
             2 AVF BCD                       1
             2 omar Ahmed Majid              2
             3 BBG HUH ABCE                  2
             3 JHU KK MNK                    1
    Edited by: 973907 on Dec 13, 2012 6:59 AM
  • 7. Re: ORA-01489: result of string concatenation is too long
    chris227 Guru
    Currently Being Moderated
    with data as (
     select
      id
     ,gen_value||',' gen_value
     from a
    )
    ,r  (id, l, gen_value) as (
     select  
      id
     ,substr(gen_value, 1, instr(gen_value, ',') - 1)
     ,substr(
        gen_value
       ,instr(gen_value, ',') + 1
      )
    from data
    union all
     select  
      id
     ,substr(gen_value, 1, instr(gen_value, ',') - 1)
     ,substr(
        gen_value
       ,instr(gen_value, ',') + 1
      )
    from r
    where
    instr(gen_value, ',') >= 0
    )
    , cnts as ( 
    select 
     soundex(substr(l, instr(l,' ', -1) +1)) s
    ,count(*) cnt
    ,max(l) keep ( dense_rank last order by length(l), l desc nulls first) v
    ,id
    from r
    group by
     soundex(substr(l, instr(l,' ', -1) +1)), id
    )
    
    select
     id
    ,v
    ,cnt
    from cnts
    order by id
    
    ID V CNT 
    1 ACD BCD AAA 2 
    1 BDE AAC AFC 1 
    1 AFD BCF 1 
    1 Egypt 3 
    1 20-11-2010 4 
    2 GACD MNF BCV 2 
    2 AVF BCD 1 
    2 omar Ahmed Majid 2 
    3 BBG HUH ABCE 2 
    3 JHU KK MNK 1 
    there is no soundex for digits. How to handle them?
  • 8. Re: ORA-01489: result of string concatenation is too long
    976910 Newbie
    Currently Being Moderated
    Yes , dear Chris for the digits the Soundex is null so , we will do like this

    NVL(soundex(words),words).

    that mean if the word is NULL that mean digit so we put the digit as it is.
    So when we have two digit same whe count function will count it .

    for example
    id   word        soundex_word 
    1   Egypt        E213
    1  Egpt          E213
    1  88              88 
    1  1-1-2012     1-1-2012  
    1  1-1-2012     1-1-2012
    then the counting will be soundex_word column.

    this is only example.
    that mean we have to used the NVL function for NULL because the soundex return NULL for digit, SO in NVL(Soundex(STR),STR) then count.
    Something like this
    COUNT(*) OVER (PARTITION BY id, NVL(SOUNDEX(str), str)) cnt
    but where can i use it.

    Please help me to update this idea to the gave SQL Code from you.

    Regards

    Edited by: 973907 on Dec 13, 2012 8:19 AM
  • 9. Re: ORA-01489: result of string concatenation is too long
    chris227 Guru
    Currently Being Moderated
    The identifier words isnt in anymore since some postings.
    Guess you mean with words the whole part between two commas.
    If it should be the the most rigth part only, replace it with the substring construct in the soundex.
    with data as (
     select
      id
     ,gen_value||',' gen_value
     from a
    )
    ,r  (id, l, gen_value) as (
     select  
      id
     ,substr(gen_value, 1, instr(gen_value, ',') - 1)
     ,substr(
        gen_value
       ,instr(gen_value, ',') + 1
      )
    from data
    union all
     select  
      id
     ,substr(gen_value, 1, instr(gen_value, ',') - 1)
     ,substr(
        gen_value
       ,instr(gen_value, ',') + 1
      )
    from r
    where
    instr(gen_value, ',') >= 0
    )
    , cnts as ( 
    select 
     nvl(soundex(substr(l, instr(l,' ', -1) +1)), l) s
    ,count(*) cnt
    ,max(l) keep ( dense_rank last order by length(l), l desc nulls first) v
    ,id
    from r
    group by
     nvl(soundex(substr(l, instr(l,' ', -1) +1)), l), id
    )
    
    select
     id
    ,v
    ,cnt
    from cnts
    order by id
    
    ID     V     CNT
    1     88     1
    1     1-1-2010     2
    1     20-11-2010     1
    1     ACD BCD AAA     2
    1     BDE AAC AFC     1
    1     AFD BCF     1
    1     Egypt     3
    2     GACD MNF BCV     2
    2     AVF BCD     1
    2     omar Ahmed Majid     2
    3     BBG HUH ABCE     2
    3     JHU KK MNK     1
  • 10. Re: ORA-01489: result of string concatenation is too long
    976910 Newbie
    Currently Being Moderated
    Many thanks for you i will test the code.
  • 11. Re: ORA-01489: result of string concatenation is too long
    AlbertoFaenza Expert
    Currently Being Moderated
    973907 wrote:
    INSERT INTO b (id, gen_value, cnt)
    *
    ERROR at line 1:
    ORA-01489: result of string concatenation is too long
    Hi, could you please post sample data to reproduce this problem?

    Regards.
    Al
  • 12. Re: ORA-01489: result of string concatenation is too long
    AlbertoFaenza Expert
    Currently Being Moderated
    973907 wrote:
    ERROR at line 1:
    ORA-01489: result of string concatenation is too long
    Did you maybe change your input table definition compare to your previous post?
    It looks that gen_value after REPLACE is having more than 200 bytes otherwise this message cannot be explained.

    In this case you can overcome this problem by using TO_CLOB function before replace in this way:
    INSERT INTO b (id, gen_value, cnt)
    WITH got_values AS
    (
       SELECT id, TRIM((column_value).getstringval()) strorig
            , REGEXP_SUBSTR(TRIM((column_value).getstringval()),'\S+$') str
         FROM a, xmltable(('"'||REPLACE(TO_CLOB(gen_value),',','","')||'"'))  -- this line is changed
    )
    /* ...*/
    In the previous post your input table a has a column gen_value defined as VARCHAR2(200).
    If the definition now is changed you need to specify:
    a) new definition of gen_value
    b) maximum length of values between each comma

    I guess that if you are having this problem because your column is now having a larger size.
    Please post sample data (CREATE TABLE and INSERT statements) to reproduce the problem if it is not fixed in this way.

    Regards.
    Al

    Edited by: Alberto Faenza on Dec 13, 2012 6:09 PM
  • 13. Re: ORA-01489: result of string concatenation is too long
    AlbertoFaenza Expert
    Currently Being Moderated
    odie_63 wrote:
    What's the database version? (SELECT * FROM v$version)

    What's the max length of GEN_VALUE?

    Dynamic XQuery is unscalable, this shouldn't be used, and anyway doesn't work on the latest versions :
    xmltable(('"'||REPLACE(gen_value,',','","')||'"'))
    Hi Odie,

    I know that you are XMLDB expert. Could you explain better your statement?

    Regards.
    Al
  • 14. Re: ORA-01489: result of string concatenation is too long
    AlbertoFaenza Expert
    Currently Being Moderated
    973907 wrote:

    I face this error :
    INSERT INTO b (id, gen_value, cnt)
    *
    ERROR at line 1:
    ORA-01489: result of string concatenation is too long
    After seeing the comment from odie_63, which has not answered yet, and your problem I guess that changing your statement in this way should solve the error problem and will avoid using dynamic Xquery:
    INSERT INTO b (id, gen_value, cnt)
    WITH got_values AS
    (
        SELECT id, TRIM(REGEXP_SUBSTR(gen_value,'[^,]+',1,LEVEL)) strorig
             , REGEXP_SUBSTR(gen_value,'([^, ]*)\s*(,|$)',1,LEVEL,'m',1) str
          FROM a
       CONNECT BY LEVEL <= REGEXP_COUNT(gen_value,',')+1
                  AND id = PRIOR id
                  AND PRIOR DBMS_RANDOM.RANDOM IS NOT NULL 
    )
    , got_sdx_cnt AS
    (
       SELECT id, strorig
            , COUNT(*) OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig)) cnt
            , ROW_NUMBER() OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig) 
                                     ORDER BY LENGTH(str) DESC, ROWNUM) rn
         FROM got_values
    )
    SELECT id, strorig, cnt 
      FROM got_sdx_cnt
     WHERE rn=1;
    Please try this and let me know if your problem is fixed.
    If not post sample date (CREATE TABLE and INSERT statements).

    Regards.
    Al

    Edited by: Alberto Faenza on Dec 13, 2012 7:26 PM
    DBMS_RANDOM.VALUE replaced with DBMS_RANDOM.RANDOM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points