Avoid Errors due to Concatenated Values Generated by the LISTAGG Function

Before You Begin

This 15-minute tutorial shows you how to avoid errors due to concatenated values generated by the LISTAGG function in a query, exceeding the maximum length supported by VARCHAR2.

Background

Oracle Database 12.2 introduced extensions to the LISTAGG function to truncate concatenated string when the combined length of values generated exceeded the maximum length supported by VARCHAR2.

In Oracle Database 19c, you can use a new extension of the LISTAGG function to eliminate duplicate values.

What Do You Need?

Oracle Database 19c installed
A database, either non-CDB or CDB with a PDB
The create_cities.sql SQL script. Download the SQL script to the labs directory created on your server /home/oracle/labs
The control_cities.ctl SQL*loader control file. Download the SQL*loader control file to the labs directory created on your server /home/oracle/labs
The cities.tbl SQL*loader data file. Download the SQL*loader data file to the labs directory created on your server /home/oracle/labs

Create and Load the Table

sqlplus system@PDB1
Enter password: password

Execute the create_cities.sql SQL script to create the table HR.CITIES.
```
@/home/oracle/labs/create_cities.sql
```
```
EXIT
```

Launch SQL*Loader to load the table HR.CITIES. Use the


                      control_cities.ctl

SQL*Loader control file and the


                      cities.tbl

SQL*Loader data file to insert rows into the table HR.CITIES.

sqlldr hr@PDB1 control=/home/oracle/labs/control_cities.ctl
Enter password: password

Commit point reached - logical record count 250
Commit point reached - logical record count 492
Commit point reached - logical record count 493

Table HR.CITIES: 493 Rows successfully loaded.
Check the log file: control_cities.log for more information about the load.

Query The Table Using The `LISTAGG` Function

sqlplus hr@PDB1
Enter password: password

Query the HR.CITIES table to order the rows by city code and use the LISTAGG function to concatenate the resulting city names for each city code into a single string.

SELECT code, LISTAGG(name, ',') WITHIN GROUP (ORDER BY code)     
FROM   hr.cities
GROUP BY code
ORDER BY code;
ERROR:
2   3   4  
ORA-01489: result of string concatenation is too long

no rows selected

Remark: Adding ON OVERFLOW ERROR to the LISTAGG function makes it completely clear that an errir is expected when an overflow happens.

SELECT code, LISTAGG(name, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY code)     
FROM   hr.cities
GROUP BY code
ORDER BY code;
ERROR:
2   3   4  
ORA-01489: result of string concatenation is too long

no rows selected

Use the clause for the LISTAGG function that truncates the string to fit within the limit of the VARCHAR2 object.
```
SELECT code, LISTAGG (name, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) 
             WITHIN GROUP (ORDER BY code) AS CITIES    
FROM  hr.cities
GROUP BY code
ORDER BY code;
```
Read the result from the result1 text file. The LISTAGG function can be used with the ON OVERFLOW TRUNCATE clause. The value defined with the ON OVERFLOW TRUNCATE clause is the value that is displayed to replace the missing values, which is '…'. This clause truncates the list of values at the 4K or 32K boundary. Concatenated values are not splitted. When determining where to force the truncation, the full length of each value is taken into account. If the value would be the contatenation of two columns, (i.e. first name and last name), there has to be enough space to add the complete string (first and last name) to the list otherwise the whole string is removed and the truncation indicator is inserted. It is not possible for the last value in the string to be only the first name where the last name has been truncated or removed.
Re-execute the query to get the number of missing values.
```
SELECT code, LISTAGG (name, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) 
             WITHIN GROUP (ORDER BY code) AS CITIES    
FROM  hr.cities
GROUP BY code
ORDER BY code;
```
Read the result from the result2 text file. Observe that the information of the number of missing values takes characters in place of possible values (Lima46 and Lima47). The overflow functionality traverses backwards from the maximum possible length to the end of the last complete value, then it adds the user-defined separator followed by the user defined overflow indicator, followed by output from the WITH COUNT clause which adds a counter at the end of a truncated string to indicate the number of values that have been removed or truncated from the list.
Re-execute the query to eliminate the duplicate values for all codes from the specified expression before concatenating the values into a single string.
```
SELECT code, LISTAGG (DISTINCT name, ',' ON OVERFLOW TRUNCATE 'other values') 
             WITHIN GROUP (ORDER BY code) AS CITIES    
FROM  hr.cities
GROUP BY code
ORDER BY code;
```
Read the result from the result3 text file. Observe that two identical strings such as 'Paris' and 'Paris ' with trailing spaces are treated as duplicates. The DISTINCT and ON OVERFLOW TRUNCATE clauses used together allow the elimination of duplicates before the concatenation of the values into a single string. This reduces the chance to miss values as is the case in the example where Lima48 and Lima49 are now displayed. Note: Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

Clean Up the Environment

Drop the table HR.CITIES table.
```
DROP TABLE hr.cities PURGE;
```
Quit the session.
```
EXIT
```
Remove the SQL script, the SQL*Loader control and data files.
```
rm /home/oracle/labs/*cities*
```

Avoid Errors due to Values Generated by the LISTAGG Function