OraFAQ Forum: SQL & PL/SQL » help around LIST

Home » SQL & PL/SQL » SQL & PL/SQL » help around LIST_AGG function (oracle 11gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help around LIST_AGG function [message #677363]

Fri, 13 September 2019 09:37

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

Report message to a moderator

Re: help around LIST_AGG function [message #677364 is a reply to message #677363]

Fri, 13 September 2019 10:30

Michel Cadot
Messages: 68417
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

rajeshkr
Messages: 5
Registered: August 2019

Junior Member

Hi Michel,

Does STRAGG function concatenate the string in the order it arrive.

Report message to a moderator

Re: help around LIST_AGG function [message #677391 is a reply to message #677390]

Mon, 16 September 2019 10:22

Michel Cadot
Messages: 68417
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.

Report message to a moderator

Re: help around LIST_AGG function [message #677406 is a reply to message #677391]

Tue, 17 September 2019 08:50

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

Report message to a moderator

Previous Topic:	data masking
Next Topic:	getting informations about processes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 22 08:05:57 CDT 2023