Inaccurate error "ORA-01489: result of string concatenation is too long" when joining two-CodePudding

My query makes a lot of use of the LISTAGG function to string together various fields with a tilde delimiter. The individual subquery SELECTs throughout the query for various single columns run just fine with my ten test customers.

When the query is run, using an INNER JOIN on ZZ_TEST_ALL_CUSTOMERS to the main CUSTOMER table, to restrict the query to just my ten test customers, it gets an error after about a minute. The error is:

ORA-01489: result of string concatenation is too long

Doing some research, this happens when the resulting string from the LISTAGG function is greater than 4000 bytes. (I was told our Oracle database is not the UTF character set so our limit is 4000 characters.) Regardless, with my test dataset the longest concatenated value from all the LISTAGG statments in the query (many), is less than 200 characters.

As an experiment we inserted the "and CUSTOMER.CUST_ID in ..." clause below the join to ZZ_TEST_ALL_CUSTOMERS with the ten test CUST_ID values hard-coded. It worked! No error and all the data pulled fine.

The lookup table ZZ_TEST_ALL_CUSTOMERS only contains the CUST_IDs for the ten test customers. When this query is run for real against the larger database, we'll need to use this ZZ_TEST_ALL_CUSTOMERS table for the IDs. We won't be able to hard-code it.

Why would an ORA-01489 error kick out when we're joining the two tables to filter the records, yet work fine if we hard-code the values?

The relevant sample of code....

SELECT.......

<cut>

    ,COALESCE(CUSTOMER_3.new_yn, '') as "New Customer"
    ,COALESCE(CUSTOMER_2.preapproved_yn, '') as "Pre-Approved"
FROM CUSTOMER
    INNER JOIN ZZ_TEST_ALL_CUSTOMERS CustomerSubset 
        ON CustomerSubset.CUST_ID = CUSTOMER.CUST_ID
        and CUSTOMER.CUST_ID in ('MI00111','MI003844','OH33928','PA39299','PA239','PA65248','PA85002','NC8585547','MI4480','ME2221')
    INNER JOIN CUSTOMER_2 
        ON CUSTOMER_2.CUST_ID = CUSTOMER.CUST_ID
    INNER JOIN CUSTOMER_3 
        ON CUSTOMER_3.CUST_ID = CUSTOMER.CUST_ID

<cut>

-- Example of one of numerous LISTAGG uses
    LEFT OUTER JOIN 
        (SELECT CUST_ID
            ,LISTAGG(EMAIL_ADDRESS, '~') WITHIN GROUP (ORDER BY EMAIL_ADDRESS) as EmailAddress
        FROM CUST_EMAILADDRESS
        GROUP BY CUST_EMAILADDRESS.CUST_ID
        ) EmailAddresses ON EmailAddresses.CUST_ID = CUSTOMER.CUST_ID

CodePudding user response：

Here are some things to try. NO_MERGE hint to force Oracle to do the group by before joining to your outer query blocks, DISTINCT within the LISTAGG function to remove any duplicates, and ON OVERFLOW TRUNCATE to prevent the error no matter what you try to string together.

. . .LEFT OUTER JOIN
              (SELECT /*  NO_MERGE */ CUST_ID ,
                     LISTAGG(DISTINCT EMAIL_ADDRESS, '~' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY EMAIL_ADDRESS) AS EmailAddress
                FROM CUST_EMAILADDRESS
            GROUP BY CUST_EMAILADDRESS.CUST_ID ) EmailAddresses
                  ON EmailAddresses.CUST_ID = CUSTOMER.CUST_ID