CODEWITHSUNDEEP
oracle-database
Saroj
Saroj

Reputation: 71

Oracle Convert table rows to list

I have a table A with single column 

 **TableA**
  Row1
  Row2
  Row3
  .....

I have to convert the table rows into a list and store them in variables. So that they are stored as

Row1,Row2,Row3,....,Rown

I used the listagg () function to achieve the solution.

DECLARE
tbl_list CLOB; 

BEGIN

SELECT  listagg (''''||Column_name||'''',',') WITHIN GROUP (ORDER BY Column_name) INTO TBL_LIST FROM TableA;  
END;

This works fine if the TableA has few rows. But if the table has lots of rows I get the following error

ORA-01489: result of string concatenation is too long

Is this due to the storage limit of variable TBL_LIST? Can anyone explain me what is wrong. And is there an alternate for lisagg(), to achieve the result, I want?

Upvotes: 1

Views: 626

Answers (2)

A. Gunasekaran
A. Gunasekaran

Reputation: 1

If you expect the result of aggregation to be more than 4000 bytes? If so, you could potentially create a user-defined aggregate function that returns a CLOB rather than a VARCHAR2. If you don't expect the result to exceed 4000 bytes there is probably something wrong with the way you've specified the aggregate.

Please refer to the "https://oracle-base.com/articles/misc/string-aggregation-techniques" for user defined aggregation.

Upvotes: 0

Praveen
Praveen

Reputation: 9335

listagg function is limited to 4000 characters, if it exceeds 4000 character yo may get an error ORA-01489: result of string concatenation is too long

You can use XMLAGG which is not limited to 4000 char.

SELECT 
    RTRIM(XMLAGG(XMLELEMENT(E,''''||Column_name||'''',',').EXTRACT('//text()') 
    ORDER BY Column_name).GetClobVal(),',') 
from TableA;

you can refer this question: How to tweak LISTAGG to support more than 4000 character in select query?

Upvotes: 1

Related Questions