If there is a need to concatenate string, there are multiple options to concatenate string if length is up to 4000 characters but if the length is more than 4000 characters then it becomes necessary to think about a different approach.
In below example, more than 4000 characters string has been concatenated using CLOB data type.
DECLARE
LC_LARGE_STRING CLOB;
BEGIN
WITH CTE_DATA AS
(
SELECT LEVEL AS TEST_ID FROM DUAL CONNECT BY LEVEL < 6000
)
SELECT
TRIM(TRAILING ',' FROM
XMLAGG(
XMLELEMENT(E,TEST_ID,',').EXTRACT('//TEXT()')
).GETCLOBVAL()
)
AS CONCATENATED_STRING
INTO LC_LARGE_STRING
FROM CTE_DATA;
DBMS_OUTPUT.PUT_LINE('STRING LENGTH:'||LENGTH(LC_LARGE_STRING));
END;
