CODEWITHSUNDEEP
sqloracle-databaseoracle12clistagg
Random guy
Random guy

Reputation: 923

Error while using listagg in oracle "STRING concatenation too long"

I have a table like temp_a which has:

acc ai_tab   where
A   B        QQQQ
A   B        RRRR
C   D        SSSS
C   D        TTTT

The column where has too large string stored. so,My expected output is

acc ai_tab where 
A   B      QQQQ RRRR
C   D      SSSS TTTT

I tried to achieve this using:

 select acc,ai_tab,LISTAGG(WHERE,'') WITHIN GROUP ORDER BY (acc) "where_cond2" from temp_a
group by acc,ai_tab;

I got the error as:

ORA-01489 :result of string concatenation is too long.

I searched this similar question and its says use the XMLCLOB also but its not working?Can we use function to get this or is there any other methods?

Upvotes: 0

Views: 500

Answers (2)

vishnudattan
vishnudattan

Reputation: 476

Agree with Littlefoot's solution. Alternatively, you could perhaps write your own function. I've included a sample below. Note: I haven't tested this with your data, so not sure how well this would perform

    SQL> create table foo as
         select 'A' acc, 'B' ai_tab, 'QQQQ' where_column from dual union all
         select 'A','B','RRRR' from dual union all
         select 'C','D','SSSS' from dual union all
         select 'C','D','TTTT' from dual;

    Table created.

    SQL> select * from foo;

    ACC AI_TAB WHERE_COLUMN
    --  --     ----
    A   B      QQQQ
    A   B      RRRR
    C   D      SSSS
    C   D      TTTT

    SQL> create or replace function str_concat (
             p_acc       in  foo.acc%type,
             p_ai_tab    in  foo.ai_tab%type
             )
         return clob
         is
             l_concat  clob;
         begin
         for o in ( 
                 select where_column from foo where acc = p_acc and ai_tab =  p_ai_tab  
             ) loop
                     l_concat := l_concat || ' ' || o.where_column;
                 end loop;
             return ltrim(l_concat, ' ');
         end;
         /

    Function created.

    SQL> select acc, ai_tab, where_column from (
         select acc, ai_tab, str_concat(acc, ai_tab) where_column, row_number() over (partition by acc, ai_tab order by null)
         ) where rn = 1;

    ACC AI_TAB  WHERE_COLUMN
    --  --      ----
    A   B       QQQQ RRRR
    C   D       SSSS TTTT

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

Here's an example which shows both options you might use; as listagg fails, try xmlagg instead.

SQL> SELECT RTRIM (
  2            XMLAGG (XMLELEMENT (e, ename || ' ') ORDER BY empno).EXTRACT (
  3               '//text()'),
  4            ', ')
  5            employees_1,
  6         --
  7         LISTAGG (ename, ' ') WITHIN GROUP (ORDER BY empno) employees_2
  8    FROM emp
  9   WHERE deptno = 10;

EMPLOYEES_1                    EMPLOYEES_2
------------------------------ ------------------------------
CLARK KING MILLER              CLARK KING MILLER

SQL>

Upvotes: 2

Related Questions