Oracle DB version 11.2.0.4.0.
I have written the below anonymous block for writing more that 32767 CLOB characters with UTL_FILE File.
I have used to cut the string and write with chunk but getting the error.
DECLARE
v_file_name VARCHAR2 (200);
RESULT CLOB;
f UTL_FILE.file_type;
v_Clob_Length INTEGER;
v_Pos INTEGER := 1;
v_Total_Lth INTEGER := 32767;
v_Buffer CLOB;
BEGIN
v_file_name := 'Data_File' ||'.txt';
f := UTL_FILE.fopen ('EXT_TAB_DIR', v_file_name, 'w', 32767);
SELECT FILE_CONTENT
INTO RESULT
FROM KALPATARU_CLOB;
v_Clob_Length := DBMS_LOB.getlength (RESULT);
WHILE v_Pos < v_Clob_Length LOOP
v_Buffer := Null;
v_Buffer := SUBSTR(RESULT,v_Pos,v_Total_Lth);
v_Pos := v_Pos + v_Total_Lth;
UTL_FILE.Put(f,v_Buffer);
UTL_FILE.fflush (f);
END LOOP;
UTL_FILE.fclose (f);
EXCEPTION
WHEN UTL_FILE.Write_Error
THEN
DBMS_OUTPUT.put_line ('Write Error'||SQLERRM);
END;
/
Error
-------
write_error
ORA-29285: file write error
Where is the issue ?