1 2 Previous Next 26 Replies Latest reply: Dec 17, 2012 3:08 AM by chris227 Go to original post RSS
      • 15. Re: ORA-01489: result of string concatenation is too long
        chris227
        simplest reason
        BANNER
        Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
        PL/SQL Release 11.2.0.3.0 - Production
        CORE 11.2.0.3.0 Production
        TNS for Linux: Version 11.2.0.3.0 - Production
        NLSRTL Version 11.2.0.3.0 - Production
        
        ORA-02000: missing COLUMNS keyword
        I still wonder why you are not satisfied with the recursive subquery solution.
        Instead you will force to use regexp and now connect by. Why?

        However, i would prefer another datastructur anyway ;-)

        Edited by: chris227 on 13.12.2012 10:21
        • 16. Re: ORA-01489: result of string concatenation is too long
          976910
          I have table more than 4000 rows , I don't know actual where this problem occur.
          Regards
          • 17. Re: ORA-01489: result of string concatenation is too long
            976910
            Thanks it's working fine
            • 18. Re: ORA-01489: result of string concatenation is too long
              976910
              I will test the to-clob. For the column size I will check it then I. Will let you know. currently I am far from my pc. Anyway, many thanks for you.
              • 19. Re: ORA-01489: result of string concatenation is too long
                chris227
                The solution with xmltable is not supported in higher versions as shown above.
                Why releying on some xdb-feature if not necessary at all?
                • 20. Re: ORA-01489: result of string concatenation is too long
                  AlbertoFaenza
                  chris227 wrote:
                  simplest reason
                  BANNER
                  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                  PL/SQL Release 11.2.0.3.0 - Production
                  CORE 11.2.0.3.0 Production
                  TNS for Linux: Version 11.2.0.3.0 - Production
                  NLSRTL Version 11.2.0.3.0 - Production
                  
                  ORA-02000: missing COLUMNS keyword
                  I see, that's clear now. I'm running 11.2.0.1 in my local laptop.
                  I still wonder why you are not satisfied with the recursive subquery solution.
                  Instead you will force to use regexp and now connect by. Why?
                  Oh, did I say that? :-)
                  I was actually providing a fix to my code but actually I have to admit that your solution is by far more efficient.
                  I think is a really good solution which I have never seen before and need to understand.

                  Using SUBSTR/INSTR instead of REGEXP is definitely faster so nothing to say but simply try to learn from your solution.

                  Thanks.

                  Regards.
                  Al
                  • 21. Re: ORA-01489: result of string concatenation is too long
                    AlbertoFaenza
                    973907 wrote:
                    I will test the to-clob. For the column size I will check it then I. Will let you know. currently I am far from my pc. Anyway, many thanks for you.
                    Please don't! Solution provided by Chris is by far more efficient than mine.

                    Regards.
                    Alberto.
                    • 22. Re: ORA-01489: result of string concatenation is too long
                      odie_63
                      Hi Al,
                      Could you explain better your statement?
                      Dynamic XQuery forces functional evaluation by the XQuery engine and cannot be optimized by the CBO.
                      Additionally, starting with 11.2.0.2, Oracle has tighten the security and now only allows to pass a pre-computed expression (column alias or bind variable).

                      This is better (as far as XQ is concerned) :
                      SQL> select a.id, trim(x.item)
                        2  from a
                        3     , xmltable(
                        4         'ora:tokenize($str, ",")'
                        5         passing a.gen_value || ',' as "str"
                        6         columns item varchar2(200) path '.'
                        7       ) x
                        8  where x.item is not null ;
                       
                              ID TRIM(X.ITEM)
                      ---------- --------------------------------------------------------------------------------
                               1 ACD BCD AAA
                               1 AFD BCF
                               1 88
                               1 Egypt
                               1 BDE AAC AFC
                               1 1-1-2010
                               1 20-11-2010
                               1 BCF AAE
                               1 Egypt
                               1 Egpt
                               1 1-1-2010
                               2 AVF BCD
                               2 omar Ahmed Majid
                               2 BBA BBG BCEV
                               2 ALI Majid
                               2 GACD MNF BCV
                               3 AFC ABC
                               3 BBG HUH ABCE
                               3 JHU KK MNK
                       
                      19 rows selected
                       
                      The XQuery expression is a string literal, therefore the CBO can recognize the tokenize function at parse time and optimize the whole query using the built-in implementation of the function.
                      In this case, the query is rewritten to :
                      +(reformatted from a CBO trace)+
                      Final query after transformations:******* UNPARSED QUERY IS *******
                      SELECT "A"."ID" "ID"
                          ,  TRIM(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,20971520,0),50,1,2) AS varchar2(200) )) "TRIM(X.ITEM)" 
                      FROM "DEV"."A" "A"
                          ,TABLE("SYS"."XQSEQUENCE"(SYS_XQTOKENIZE("A"."GEN_VALUE"||',',','))) "KOKBF$" 
                      WHERE CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,20971520,0),50,1,2) AS varchar2(200) ) IS NOT NULL
                      • 23. Re: ORA-01489: result of string concatenation is too long
                        976910
                        thanks Chris277 , the query working well. that what i need.
                        So,

                        I want to do it for each row that mean when user enter the row execute the SQL-Code.
                        I have create trigger for that job. but i face probelm. when i select the data from second table zero rows.

                        the problem is:

                        my trigger is before insert , So the data not save to table i tried to used DUAL table but also zero rows in table B.
                        drop table a;
                        CREATE TABLE  a(id NUMBER(9), gen_value VARCHAR2(200));
                         
                        
                        drop table b;
                        CREATE TABLE  b(id NUMBER(9), Gen_Value VARCHAR2(200), cnt NUMBER(9));
                        create or replace trigger get_value_count
                          before insert 
                        on a
                          for each row
                        declare
                        begin
                        
                        insert into b(id, gen_value, cnt) 
                        with data as (
                         select
                          id
                         ,gen_value||',' gen_value
                         from a where id=:new.id                  -- I though the probem id here becasue no row commit i tried to change it from dual but also zero rows
                        )
                        ,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;
                        
                        end;
                        /
                        the is the insert statment
                        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');
                        
                        COMMIT;
                        the selection from B table.
                        select * from b;
                        • 24. Re: ORA-01489: result of string concatenation is too long
                          976910
                          Many thanks for you.

                          Edited by: 973907 on Dec 15, 2012 10:15 PM
                          • 25. Re: ORA-01489: result of string concatenation is too long
                            976910
                            many thanks for all.
                            • 26. Re: ORA-01489: result of string concatenation is too long
                              chris227
                              First of all i would propose to open a new thread, since this question is on a different topic.
                              Furthermore i would ask myself if there is no other way than using a trigger.
                              For example using a pl/sql-procedure as a table api, or using a view with an instead-of-trigger using a pl/sql-function.
                              Triggers have some drawbacks:
                              - Often it is difficult to debug code with triggers
                              - sometimes the effects or sideeffects of trigger are overlooked (even the existence ;-)


                              So the use of a pl/sql-api to apply such a business logic is, in my opinion, a good common practise, which is preferable to the usage of triggers.
                              Usage of triggers should constrainted to use only for simple, atomic things.
                              1 2 Previous Next