Home » SQL & PL/SQL » SQL & PL/SQL » help around LIST_AGG function (oracle 11gr2)
help around LIST_AGG function [message #677363] Fri, 13 September 2019 09:37 Go to next message
rajeshkr
Messages: 5
Registered: August 2019
Junior Member
Dear Oracle guru,

I am using LIST_AGG function to concatenate several rows data value into 1 column value(max 20 value row so far), all of a sudden 245 rows occurred and it has resulted into fail the loading process reason being
ORA-01489: result of string concatenation is too long.

Can some body suggest me a good work around which can fix the above error in case it appear again in future.

This would be great help.

I am trying to split the rows in 30 and concatenating for that is not the generalize soltion and not a good way to load the data.

Looking for the good suggest desperately

Thanks
Re: help around LIST_AGG function [message #677364 is a reply to message #677363] Fri, 13 September 2019 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 66632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you talking about standard LISTAGG function?
As it returns a VARCHAR2, it cannot exceed 4000 bytes in SQL.

I posted a similar function there, it is derived from T. Kyte's STRAGG function but returns a CLOB and so is not restricted.

[Updated on: Fri, 13 September 2019 10:31]

Report message to a moderator

Re: help around LIST_AGG function [message #677390 is a reply to message #677364] Mon, 16 September 2019 09:50 Go to previous messageGo to next message
rajeshkr
Messages: 5
Registered: August 2019
Junior Member
Hi Michel,

Does STRAGG function concatenate the string in the order it arrive.
Re: help around LIST_AGG function [message #677391 is a reply to message #677390] Mon, 16 September 2019 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As STRAGG can run in parallel "order" is meaningless.
Remove the PARALLEL_ENABLE option if you want the stribg to be concatenate "as they arrive" which actually means nothing in relational SQL (2 executions of the same query can return the rows in different order) unless you specify an ORDER BY somewhere before.

Re: help around LIST_AGG function [message #677406 is a reply to message #677391] Tue, 17 September 2019 08:50 Go to previous message
rajeshkr
Messages: 5
Registered: August 2019
Junior Member
Thanks Michael,

I have to implement PARALLEL_ENABLE option and order by clause to see if they work. I will post my finding soon
Previous Topic: data masking
Next Topic: getting informations about processes
Goto Forum:
  


Current Time: Sun Oct 13 17:21:00 CDT 2019