While testing a new SQL statement using LISTAGG, I noticed one scenario where an extra CR/LF is being added to the result set. I've tested the following against 11.2.04, 12.1.0.2 and 19.8 (livesql.oracle.com) and results appear the same for all. For 11.2.0.4/12.1.0.2, I'm using PL/SQL Developer so I can see the HEX values in the result set, hence how I confirmed the extra CR/LF (hex 0DOA).
Here's the basic statement
WITH victims as (select 1556 chrg_id_seq, 'SIBL' rela_cd from dual union all
select 1556, 'OTHR' from dual),
relationship_codes as (select 'SIBL' rela_cd, 'SIBLING' descr from dual union all
select 'OTHR', 'OTHER PERSON LIVING IN HOUSEHOLD' from dual)
SELECT listagg('Relation to victim: ' || rc.descr, CHR(10)||chr(13)) WITHIN GROUP (ORDER BY rc.descr) vctm_info
FROM victims v
INNER JOIN relationship_codes rc ON (v.rela_cd = rc.rela_cd)
WHERE chrg_id_seq = 1556;
The above adds a blank line between the output
Relation to victim: OTHER PERSON LIVING IN HOUSEHOLD
Relation to victim: SIBLING
It should not. The output should look like
Relation to victim: OTHER PERSON LIVING IN HOUSEHOLD
Relation to victim: SIBLING
By flipping around the order of the CHR values or just using a single one, there is only a single CR/LF in the output. So the result is that
chr(10)||chr(13) = 0D0A0D0A (aka extra 0D0A)
chr(13)||chr(10) = 0D0A
chr(10) = 0D0A
chr(13) = 0D0A
Anyone aware of a reason why CHR(10)||CHR(13) would add an extra CR/LF to the output of LISTAGG? I was also surprised by the fact a single one still output both the CR/LF, but could write that off as internal handling to deal with the user's desired intention.