12 Replies Latest reply: Sep 27, 2009 9:37 AM by Solomon Yakobson RSS

    sys_connect_by_path & to_CLOB

    719825
      Hi,

      I tried this query to use on my database (took it from [http://tkyte.blogspot.com/2007_03_01_archive.html|http://tkyte.blogspot.com/2007_03_01_archive.html] )
      SQL> select deptno,  
      2         max(sys_connect_by_path(ename, ' ' )) scbp  
      3    from (select deptno, ename,                     row_number() over                    (partition by deptno order by ename) rn  
      4            from emp  
      5             )  
      6  start with rn = 1  
      7  connect by prior rn = rn-1 and prior deptno = deptno  
      8  group by deptno  
      9  order by deptno 
      10  /    
      
      DEPTNO SCBP---------- ----------------------------------------        
      10          CLARK KING MILLER        
      20          ADAMS FORD JONES SCOTT SMITH        
      30          ALLEN BLAKE JAMES MARTIN TURNER WARD
      When I use the same query on my table in my database (not this one) I have a lot of results and I get this error:

      ORA-01489: result of string concatenation is too long
      (I can have between 1 et 500 entries, different lenghts)

      I made some search in the forum and Google and I think I can use the function TO_CLOB but it doesen't work. (Line 3) select deptno, (to_clob(ename)),
      SQL> select deptno,  
      2         max(sys_connect_by_path(ename, ' ' )) scbp  
      3    from (select deptno, (to_clob(ename)),                     row_number() over                    (partition by deptno order by ename) rn  
      4            from emp  
      5             )  
      6  start with rn = 1  
      7  connect by prior rn = rn-1 and prior deptno = deptno  
      8  group by deptno  
      9  order by deptno 
      10  /    
      
      ORA-00904: string: invalid identifier
      My Question: can I use to_clob in this query?
      thanks, Roseline

      Edited by: Roseline on 2009-09-25 12:06
        • 1. Re: sys_connect_by_path & to_CLOB
          Solomon Yakobson
          Roseline wrote:

          I made some search in the forum and Google and I think I can use the function TO_CLOB but it doesen't work.
          Well, sorry to disappoint you, but SYS_CONNECT_BY_PATH does not support CLOB, just VARCHAR2/CHAR. You could write own aggregation function to concatenate CLOBs (check this forum for STRAGG).

          SY.
          • 2. Re: sys_connect_by_path & to_CLOB
            Hoek
            Depending on your DB-version, using to_clob works on 10GXE, but you'll need to use a different separator:

            (Since I'm on XE at the moment, I used the HR scheme, instead of EMP/DEPT etc.)
            HR%xe> select department_id
              2  ,      ltrim(max(sys_connect_by_path(last_name, ' ' )), ' ') scbp  
              3  from ( select department_id
              4         ,      to_clob(last_name) last_name
              5         ,      row_number() over (partition by department_id order by last_name) rn  
              6         from   employees  
              7        )  
              8  start with rn = 1  
              9  connect by prior rn=rn-1
             10         and prior department_id = department_id  
             11  group by department_id  
             12  order by department_id;
                   from   employees
                          *
            FOUT in regel 6:
            .ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as
            part of column value
            
            
            HR%xe> select department_id
              2  ,      ltrim(max(sys_connect_by_path(last_name, ',' )), ',') scbp  
              3  from ( select department_id
              4         ,      to_clob(last_name) last_name
              5         ,      row_number() over (partition by department_id order by last_name) rn  
              6         from   employees  
              7        )  
              8  start with rn = 1  
              9  connect by prior rn=rn-1
             10         and prior department_id = department_id  
             11  group by department_id  
             12  order by department_id;
            
            DEPARTMENT_ID SCBP
            ------------- --------------------------------------------------
                       10 Whalen
                       20 Fay,Hartstein
                       30 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
                       40 Mavris
                       50 Atkinson,Bell,Bissot,Bull,Cabrio,Chung,Davies,Dell
                          inger,Dilly,Everett,Feeney,Fleaur,Fripp,Gates,Gee,
                          Geoni,Grant,Jones,Kaufling,Ladwig,Landry,Mallin,Ma
                          rkle,Marlow,Matos,McCain,Mikkilineni,Mourgos,Nayer
                          ,OConnell,Olson,Patel,Perkins,Philtanker,Rajs,Roge
                          rs,Sarchand,Seo,Stiles,Sullivan,Taylor,Vargas,Voll
                          man,Walsh,Weiss
            
                       60 Austin,Ernst,Hunold,Lorentz,Pataballa
                       70 Baer
                       80 Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Ca
                          mbrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Joh
                          nson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olse
                          n,Ozer,Partners,Russell,Sewall,Smith,Smith,Sully,T
                          aylor,Tucker,Tuvault,Vishney,Zlotkey
            
                       90 De Haan,King,Kochhar
                      100 Chen,Faviet,Greenberg,Popp,Sciarra,Urman
                      110 Gietz,Higgins
                          Grant
            
            12 rijen zijn geselecteerd.
            
            HR%xe> 
            • 3. Re: sys_connect_by_path & to_CLOB
              Solomon Yakobson
              hoek wrote:
              Depending on your DB-version, using to_clob works on 10GXE, but you'll need to use a different separator:
              And what does it have to do with SYS_CONNECT_BY_PATH result exceeding 4000 bytes?

              SY.
              • 4. Re: sys_connect_by_path & to_CLOB
                Hoek
                And what does it have to do with SYS_CONNECT_BY_PATH result exceeding 4000 bytes?
                :|

                oops
                Well, errrmm...actually...ummm, well: nothing at all.
                Thanks for setting things straight, Solomon.
                turns more red/redder than any tomato
                You're 100% right, I completely missed this part:
                When I use the same query on my table in my database (not this one)
                and started playing to eager with the EMP(loyees) example right away.
                A simple rpad is enough to fully blow my silly example:
                HR%xe> select department_id
                  2  ,      ltrim(max(sys_connect_by_path(last_name, ',' )), ',') scbp  
                  3  from ( select department_id
                  4         ,      to_clob(rpad(last_name, 5000, '*')) last_name
                  5         ,      row_number() over (partition by department_id order by last_name) rn  
                  6         from   employees  
                  7        )  
                  8  start with rn = 1  
                  9  connect by prior rn=rn-1
                 10         and prior department_id = department_id  
                 11  group by department_id  
                 12  order by department_id;
                       from   employees
                              *
                FOUT in regel 6:
                .ORA-01489: result of string concatenation is too long
                Therefore:
                walks over to a certain 'Hall' (again)
                • 5. Re: sys_connect_by_path & to_CLOB
                  Frank Kulash
                  Hi,

                  You can use SYS_CONNECT_BY_PATH to form parts of the string in a sub-query, and then concatenate those parts into a CLOB in the main query.

                  The example below assumes we can only put 2 itmes in a VARCHAR2. I set the number that small just for testing; you should set it as high as possible. For example, if your items can be up to 20 bytes long, and you need 1 byte for the delimiter, then you can safely cram 190 items into a 4000-byte VARCHAR2 column., and you should set <tt>:n := 190</tt>.
                  VARIABLE     n     NUMBER
                  
                  EXEC     :n := 2;
                  
                  WITH     got_rnum     AS
                  (
                       SELECT     deptno
                       ,     ename
                       ,     ROW_NUMBER () OVER ( PARTITION BY  deptno
                                                 ORDER BY          ename
                                         )  AS rnum
                       FROM    scott.emp
                  )
                  ,     got_short_path     AS
                  (
                       SELECT     deptno
                       ,     CEIL (rnum / :n)               AS n_num
                       ,     SYS_CONNECT_BY_PATH (ename, ',')     AS short_path
                       FROM     got_rnum
                       WHERE     CONNECT_BY_ISLEAF     = 1
                       START WITH     MOD (rnum, :n)     = 1
                       CONNECT BY     rnum              = PRIOR rnum + 1
                       AND          deptno              = PRIOR deptno
                       AND          MOD (rnum, :n)     != 1
                  )
                  SELECT       deptno
                  ,       TO_CLOB (MAX (CASE WHEN n_num = 1 THEN short_path END))     ||
                         TO_CLOB (MAX (CASE WHEN n_num = 2 THEN short_path END))     ||
                         TO_CLOB (MAX (CASE WHEN n_num = 3 THEN short_path END))     ||
                         TO_CLOB (MAX (CASE WHEN n_num = 4 THEN short_path END))     ||
                         TO_CLOB (MAX (CASE WHEN n_num = 5 THEN short_path END))     AS path
                  FROM       got_short_path
                  GROUP BY  deptno;
                  The query above will concatenate 5 VARCHAR2 columns together. You can make this any number, but it has to be hard-coded. You can use dynamic SQL to figure out how many you actually need and write that part of the main query for you.

                  Instead of packing the same number of enames into every short_path, it would be possible to figure out exactly how many would fit, using <tt>SUM (LENGTH (ename) + 1) OVER (PARTITION BY deptno ORDER BY rnum)</tt>, but that would be much more complex, and you could get the same effect by just concatenating more short_paths in the main query.

                  Edited by: Frank Kulash on Sep 26, 2009 5:50 AM
                  • 6. Re: sys_connect_by_path & to_CLOB
                    Solomon Yakobson
                    Frank Kulash wrote:

                    You can use SYS_CONNECT_BY_PATH to form parts of the string in a sub-query, and then concatenate those parts into a CLOB in the main query.
                    It is much simpler and completely transparent (you do not need to precalculate into how many parts to split the hierarchy) to use HIERARCHY package I wrote so many moons ago that completely forgot about it and add CLOB support:
                    CREATE OR REPLACE
                      PACKAGE Hierarchy
                        IS
                            TYPE BranchTableVarchar2Type IS TABLE OF VARCHAR2(4000)
                              INDEX BY BINARY_INTEGER;
                            BranchTableVarchar2 BranchTableVarchar2Type;
                            TYPE BranchTableClobType IS TABLE OF CLOB
                              INDEX BY BINARY_INTEGER;
                            BranchTableClob BranchTableClobType;
                            FUNCTION Branch(
                                            p_Level          IN NUMBER,
                                            p_Value          IN VARCHAR2,
                                            p_Delimiter      IN VARCHAR2 DEFAULT CHR(0)
                                           )
                              RETURN VARCHAR2;
                            PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
                            FUNCTION Branch(
                                            p_Level          IN NUMBER,
                                            p_Value          IN CLOB,
                                            p_Delimiter      IN VARCHAR2 DEFAULT CHR(0)
                                           )
                              RETURN CLOB;
                            PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
                    END Hierarchy;
                    /
                    CREATE OR REPLACE
                      PACKAGE BODY Hierarchy
                        IS
                            ReturnValueVarchar2 VARCHAR2(4000);
                            ReturnValueClob     CLOB;
                        FUNCTION Branch(
                                        p_Level        IN NUMBER,
                                        p_Value        IN VARCHAR2,
                                        p_Delimiter    IN VARCHAR2 DEFAULT CHR(0)
                                       )
                          RETURN VARCHAR2
                          IS
                          BEGIN
                              BranchTableVarchar2(p_Level) := p_Value;
                              ReturnValueVarchar2          := p_Value;
                              FOR I IN REVERSE 1..p_Level - 1 LOOP
                                ReturnValueVarchar2 := BranchTableVarchar2(I)|| p_Delimiter || ReturnValueVarchar2;
                              END LOOP;
                              RETURN ReturnValueVarchar2;
                        END Branch;
                        FUNCTION Branch(
                                        p_Level        IN NUMBER,
                                        p_Value        IN CLOB,
                                        p_Delimiter    IN VARCHAR2 DEFAULT CHR(0)
                                       )
                          RETURN CLOB
                          IS
                          BEGIN
                              BranchTableClob(p_Level) := p_Value;
                              ReturnValueClob          := p_Value;
                              FOR I IN REVERSE 1..p_Level - 1 LOOP
                                ReturnValueClob := BranchTableClob(I)|| p_Delimiter || ReturnValueClob;
                              END LOOP;
                              RETURN ReturnValueClob;
                        END Branch;
                    END Hierarchy;
                    /
                    Now:
                    SQL> select  sys_connect_by_path(ename,',') ename
                      2    from  (
                      3           select  to_clob(rpad(ename,1000,'*')) ename,
                      4                   empno,
                      5                   mgr
                      6             from  emp
                      7          )
                      8    where connect_by_isleaf = 1
                      9      and rownum = 1
                     10    start with mgr is null
                     11    connect by mgr = prior empno
                     12  /
                               from  emp
                                     *
                    ERROR at line 6:
                    ORA-01489: result of string concatenation is too long
                    
                    
                    SQL> set long 10000
                    SQL> select  hierarchy.branch(level,ename,',') ename
                      2    from  (
                      3           select  to_clob(rpad(ename,1000,'*')) ename,
                      4                   empno,
                      5                   mgr
                      6             from  emp
                      7          )
                      8    where connect_by_isleaf = 1
                      9      and rownum = 1
                     10    start with mgr is null
                     11    connect by mgr = prior empno
                     12  /
                    
                    ENAME
                    --------------------------------------------------------------------------------
                    KING****************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    
                    ENAME
                    --------------------------------------------------------------------------------
                    ********************************************************************************
                    ****************************************,CLARK**********************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    
                    ENAME
                    --------------------------------------------------------------------------------
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    *,FORD**************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    
                    ENAME
                    --------------------------------------------------------------------------------
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ******************************************,ADAMS********************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    
                    ENAME
                    --------------------------------------------------------------------------------
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ********************************************************************************
                    ***
                    
                    
                    SQL> 
                    SY.
                    • 7. Re: sys_connect_by_path & to_CLOB
                      719825
                      Hi,

                      I tried the example and I have no results:
                      ENAME 
                      -  
                      
                      1 rows returned in 0,00 seconds 
                      I did everything in SQL Workshop in Oracle Express except for the line:
                      set long 10000
                      I didn't work so I did some research and saw that I had to do it in SQLPLUS . So I connected as SYSDBA and put the line
                      set long 10000
                      Do you thing it'S why I don't have any results in return?

                      thanks,

                      Roseline
                      • 8. Re: sys_connect_by_path & to_CLOB
                        Frank Kulash
                        Hi,

                        If you had a problem with SET LONG, then you wouldn't see all of the output, but you would see the first part of it (maybe 80 characters).

                        If you're not seeing anything, but not getting an error, then it may be because you're not using the standard scott.emp table.
                        Change the table and column names to something that you know you can read.

                        If you really want to find why Solomon's idea wasn't working for you, try this:
                        SELECT  deptno
                        ,       ename
                        FROM    scott.emp
                        WHERE   ROWNUM <= 5;
                        What results do you get? Make sure you say scott.emp (not just emp) to make sure you're not using your own table (or synonym).
                        • 9. Re: sys_connect_by_path & to_CLOB
                          719825
                          You are right. I tried the query and it dosen't work. It's not scott.emp
                          Where can I find scott.emp exact structure and data?

                          thanks again,

                          Roseline

                          Edited by: Roseline on 2009-09-26 21:08
                          • 10. Re: sys_connect_by_path & to_CLOB
                            719825
                            .
                            • 11. Re: sys_connect_by_path & to_CLOB
                              Frank Kulash
                              Hi,
                              Roseline wrote:
                              You are right. I tried the query and it dosen't work. It's not scott.emp
                              Where can I find scott.emp exact structure and data?
                              Yoiu may already have the scott schema; if so, you just have to get privileges on it.

                              Log in as SYSTEM as say:
                              GRANT SELECT ON scott.emp TO PUBLIC;
                              If that works, do the same for the other tables owned by scott, including dept and salgrade.

                              If you really don't have the scott schema, you can get it from [http://wiki.oracle.com/page/scott%2Ftiger].
                              (Thanks to Re: SCOTT/TIGER sample tables for Oracle Express ?.)
                              • 12. Re: sys_connect_by_path & to_CLOB
                                Solomon Yakobson
                                Roseline wrote:
                                You are right. I tried the query and it dosen't work. It's not scott.emp
                                Why didn't you try it on your query? Make sure user you ran your query as has CREATE PROCEDURE privilege and create hierarchy package I posted (in general you should create it under a separate user, e.g. COMMON, that owns user defined shared objects, grant execute on the package to PUBLIC and create a public synonym for it). However, even if you modify your query to use the package, quey will not run. Oracle does not support aggregate functions against CLOB:
                                SQL> create table emp1 as select to_clob(ename) ename_clob,deptno from emp
                                  2  /
                                
                                Table created.
                                
                                SQL> select deptno,max(ename_clob) from emp1 group by deptno
                                  2  /
                                select deptno,max(ename_clob) from emp1 group by deptno
                                                  *
                                ERROR at line 1:
                                ORA-00932: inconsistent datatypes: expected - got CLOB
                                
                                
                                SQL> 
                                You need to replace aggregation, which by the way is not good solution performance-wise. You are using analytic function anyway, so all you need in add analytic COUNT and select row with rn = COUNT:
                                select  deptno,
                                        scbp
                                  from  (
                                         select  deptno,
                                                 hierarchy.branch(level,ename_clob,' ' ) scbp,
                                                 rn,
                                                 cnt
                                           from  (
                                                  select  deptno,
                                                          to_clob(ename) ename_clob,
                                                          row_number() over(partition by deptno order by ename) rn,
                                                          count(*) over(partition by deptno) cnt
                                                    from  emp
                                                 )  
                                           start with rn = 1
                                           connect by prior rn = rn - 1
                                                  and prior deptno = deptno
                                        )
                                  where rn = cnt
                                  order by deptno
                                /
                                
                                    DEPTNO SCBP
                                ---------- --------------------------------------------------------------------------------
                                        10 CLARK KING MILLER
                                        20 ADAMS FORD JONES SCOTT SMITH
                                        30 ALLEN BLAKE JAMES MARTIN TURNER WARD
                                
                                SQL> 
                                SY.