1 2 Previous Next 26 Replies Latest reply: Dec 17, 2012 3:08 AM by chris227 RSS

    ORA-01489: result of string concatenation is too long

    976910
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Many thanks for you i will test the code.
                            • 11. Re: ORA-01489: result of string concatenation is too long
                              AlbertoFaenza
                              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
                                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
                                  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
                                    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