Hi Experts,
Im using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
We replaced LISTAGG() with XMLAGG() to avoid concatenation error.
when i check the lenght of charecters from both of the fuction output, XMLAGG() giving an extra char in length.
Could you please suggest me how can i overcome this issue.
Please find the below sql and out put
XMLAGG():
SELECT TO_CHAR (
SUBSTR (
XMLAGG (XMLELEMENT (e, table_name, CHR (13)).EXTRACT (
'//text()') ORDER BY tablespace_name).GetClobVal (),
1,
2000))
AS str_concate,
LENGTH (
TO_CHAR (
SUBSTR (
XMLAGG (XMLELEMENT (e, table_name, CHR (13)).EXTRACT (
'//text()') ORDER BY tablespace_name).GetClobVal (),
1,
2000)))
AS str_length
FROM all_tables
WHERE table_name = 'TEST_LOAD;
OUTPUT:
"STR_CONCATE" "STR_LENGTH"
TEST_LOAD 26
TEST_LOAD
LISTAGG()
SELECT LISTAGG (SUBSTR (table_name, 1, 2000), CHR (13))
WITHIN GROUP (ORDER BY tablespace_name)
AS str_concate,
LENGTH (
LISTAGG (SUBSTR (table_name, 1, 2000), CHR (13))
WITHIN GROUP (ORDER BY tablespace_name))
AS str_length
FROM all_tables
WHERE table_name = 'TEST_LOAD';
OUTPUT:
"STR_CONCATE" "STR_LENGTH"
TEST_LOAD 25
TEST_LOAD