ORA-01489: Concatenation || not allow to concat long string. Solution TO_CLOB

Error detail: Result of a string concatenation operation exceeded the maximum length allowed

if you use the below logic like 

CREATE OR REPLACE PROCEDURE AUTO_REPORT AS 

V_FILE_NAME VARCHAR2(4000);

BEGIN

  FOR I in (SELECT 'XYZ' || COLUMN_NAME || 'XYZ'  as Query_data FROM  ABLE1) 

    LOOP

        WRITE_FILE (TO_CHAR(I.Query_data),'V_FILE_NAME');

    END LOO;

END AUTO_REPORT;

/

Above block will gives error if XYZ text length is bigger then 4000 character. so need to change it with TO_CLOB to resolve this error.

CREATE OR REPLACE PROCEDURE AUTO_REPORT AS 

V_FILE_NAME VARCHAR2(4000);

BEGIN

  FOR I in (SELECT  TO_CLOB('XYZ') || COLUMN_NAME || TO_CLOB('XYZ')    as Query_data FROM  TABLE1) 

    LOOP

        WRITE_FILE (TO_CHAR(I.Query_data),'V_FILE_NAME');

    END LOO;

END AUTO_REPORT;

FULL CODE

============

create or replace PROCEDURE AUTO_REPORT AS 

   V_FILE_NAME VARCHAR2(4000);

BEGIN

   V_FILE_NAME := 'TEST_ONLY';

   FOR I IN (SELECT 

        TO_CLOB('XYZ SSS') || 

        TO_CLOB(EMP_NAME) || 

        TO_CLOB('XYZ') AS QUERY_DATA FROM EMPLOYEE) 

   LOOP

      WRITE_FILE(TO_CHAR(I.QUERY_DATA), V_FILE_NAME);

   END LOOP;

END AUTO_REPORT;

/

create or replace PROCEDURE WRITE_FILE (P_CONTENT IN LONG, P_FILE_NAME IN VARCHAR) AS

----L_FILE UTL_FILE.FILE_TYPE;

STRING_DATA CLOB;

SPLIT_STRING CLOB;

COUNTER_VALUE NUMBER;

LAST_OCCURANCE NUMBER;

BEGIN

DBMS_OUTPUT.ENABLE (1000000);

---L_FILE: UTL_FILE.FOPEN('FSCM AUTOMIZATION_LOCAL_DIR',P_FILE_NAME, 'A'); 

STRING_DATA:= REPLACE (P_CONTENT,'``','`') ||'`';

COUNTER_VALUE:=1;

WHILE (1=1)

LOOP

IF COUNTER_VALUE=1 THEN

LAST_OCCURANCE:= INSTR(STRING_DATA,'`',1,1);

SPLIT_STRING:= SUBSTR(STRING_DATA, 1, INSTR (STRING_DATA, '`',1,1)-1);

ELSE

SPLIT_STRING:= SUBSTR(STRING_DATA, LAST_OCCURANCE+1, INSTR(STRING_DATA,'`' ,(LAST_OCCURANCE+1),1));

SPLIT_STRING:= SUBSTR (SPLIT_STRING,1,INSTR(SPLIT_STRING,'`',1)-1);

LAST_OCCURANCE:=INSTR(STRING_DATA,'`', (LAST_OCCURANCE+1),1);

END IF;

--Means considering the string up to the end or in case of error set counter to 5000 to avoid infinite loop. 

--Please also note that P_CONTENT line should not more then 5000 line one line means one mark in content.

IF SPLIT_STRING IS NULL OR COUNTER_VALUE = 5000 THEN EXIT;

END IF;

DBMS_OUTPUT.PUT_LINE(SPLIT_STRING);

--DBMS_OUTPUT.PUT_LINE(P_CONTENT);

----UTL_FILE.PUT_LINE(L_FILE, SPLIT_STRING);

SPLIT_STRING:='';

COUNTER_VALUE:=COUNTER_VALUE+1;

END LOOP;

----UTL_FILE.FFLUSH(L_FILE);

--UTL_FILE.FCLOSE (L_FILE);

--EXCEPTION WHEN OTHERS THEN

--UTL_FILE.FFLUSH(L_FILE);

--UTL_FILE.FCLOSE (L_FILE);

--RAISE;

END WRITE_FILE;

/