Skip to Main Content
  • Questions
  • Listagg function is failing in 12c but working fine in 11g with same data

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Tannu.

Asked: July 21, 2020 - 12:16 pm UTC

Answered by: Connor McDonald - Last updated: July 24, 2020 - 3:40 am UTC

Category: SQL - Version: 12.1c

Viewed 100+ times

You Asked

Hi Tom,

Recently our database migrated from 11.2.0.4.0 to 12.1.0.2.0- production.
So currently we have two database one is 11g and another one is 12c. Once 12c issues are fixed we will shutdown 11g and expose 12c database to the customer.
After migration some of our report started failing in 12c database. Mainly i saw this issue in listagg function. Initially i thought the data length is more than 4000 that is why it is failing but when i started investigating i found same data is working fine in 11g and data length is around 3200 which is much lesser than 4000

I used dump function to check incase it is considering input type as raw. But to my surprise it is varchar2. And dump function is not throwing any error where on the same data even length function is throwing error.

Ora error is:-
Ora-01489: result of string concatenation is too long

I checked the MAX_STRING_SIZE is set to Standard

I tried with different dataset and found whenever the input data length is more than 2000 we are getting this error in 12c for this query.

I have requested dba team to set MAX_STRING_SIZE to Extended (and utl32k.sql) but i am not very sure about the solution if that will work as i did not get the root cause of the problem. But i am afraid if we change this parameter value to extended then we won't be able to change it back to standard and if we face any issue then we have to rebuild the database. Which will be a big problem.

I need your help and valuable suggestion on this

and we said...

1) Have you changed charactersets as part of the upgrade?
2) Rather than check your data with "length" try the "lengthb" function - see if they goes over 4000

To investigate more, we'd need so see some sample data.

and you rated our response

  (5 ratings)

Reviews

July 22, 2020 - 7:35 am UTC

Reviewer: A reader

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

July 22, 2020 - 8:01 am UTC

Reviewer: A reader

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?
Connor McDonald

Followup  

July 22, 2020 - 1:35 pm UTC

Definitely not that.

But we need to see a test case if possible.

Listagg clob

July 22, 2020 - 1:50 pm UTC

Reviewer: A reader

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

July 22, 2020 - 8:49 pm UTC

Reviewer: Anton

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

July 23, 2020 - 2:40 pm UTC

Reviewer: A reader from India

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 :)
Connor McDonald

Followup  

July 24, 2020 - 3:40 am UTC

Thanks for getting back to us. This will help others.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.