A reader, July 22, 2020 - 7:35 am UTC
Hi Connor,
1. Nls_characterset is AL32UTF8 in both the databases
2. My query is fetching two columns having listagg. Both the column length is less than 4000 and when i am executing same query but fetching only one column then it is working fine for both the column but when i am trying to fetch two columns together then it is throwing error.
3. I also tried your famous listagg_clob locally but this function does not have order by clause so i first fetched the data using orderby then used listagg_clob on that data. Without order by it is working fine but when i am adding order by it is getting hanged.
Any suggestions would be very helpful
A reader, July 22, 2020 - 8:01 am UTC
Just one more thing i would like to add i.e. sga in new db is lesser than the previous one where the pga is much more in new database. Do you think that can cause a problem?
July 22, 2020 - 1:35 pm UTC
Definitely not that.
But we need to see a test case if possible.
Listagg clob
A reader, July 22, 2020 - 1:50 pm UTC
Hi Connor
This is my work related issue and i am sorry but could not reproduce this in local machine to share with you.
1. Nls_characterset is AL32UTF8 in both the databases
2. My query is fetching two columns having listagg. Both the column length is less than 4000 and when i am executing same query but fetching only one column then it is working fine for both the column but when i am trying to fetch two columns together then it is throwing error.
3. I also tried your famous listagg_clob locally but this function does not have order by clause so i first fetched the data using orderby then used listagg_clob on that data. Without order by it is working fine but when i am adding order by it is taking little longer is there any other way to use listagg clob to use order by clause as well
Any suggestions would be very helpful
Check length
Anton, July 22, 2020 - 8:49 pm UTC
Easy enough to use a query like this to check if it's a Oracle bug or data depending. Use only the sums on your data and you will see if it's less than 4000.
with my_table as (
select 'c1_' || to_char( level ) col1
, 'c2_' || to_char( level ) col2
, ',' my_separator -- use same value as in listagg
from dual
connect by level <= 587 ) -- use 301 to get a length of 1998
-- use 302 to get a length of 2005
-- use 587 to get a length of 4000
select sum( length( col1 ) + length( my_separator ) ) - max( length( my_separator ) ) length_in_chars
, sum( lengthb( col1 ) + lengthb( my_separator ) ) - max( lengthb( my_separator ) ) length_in_bytes
, listagg( col1, ',' ) within group ( order by col1 )
from my_table
It is known bug
A reader, July 23, 2020 - 2:40 pm UTC
Finally we have reached out to oracle support team. And they confirmed this is a known bug. Bug 22340408 and to fix this patch is required. This is for anyone who is facing Similar issue like me. Happy coding :)
July 24, 2020 - 3:40 am UTC
Thanks for getting back to us. This will help others.