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
CREATE OR REPLACE PROCEDURE AUTO_REPORT ASV_FILE_NAME VARCHAR2(4000);BEGINFOR I in (SELECT 'XYZ' || COLUMN_NAME || 'XYZ' as Query_data FROM ABLE1)LOOPWRITE_FILE (TO_CHAR(I.Query_data),'V_FILE_NAME');END LOO;END AUTO_REPORT;/
CREATE OR REPLACE PROCEDURE AUTO_REPORT ASV_FILE_NAME VARCHAR2(4000);BEGINFOR I in (SELECT TO_CLOB('XYZ') || COLUMN_NAME || TO_CLOB('XYZ') as Query_data FROM TABLE1)LOOPWRITE_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;
/