Got the help or code from the forum only but i need to make a small modification to the code.sample code goes something like this .
SET serveroutput ON
DECLARE
vstart NUMBER := 1;
bytelen NUMBER := 32000;
LEN NUMBER;
my_vr RAW (32000);
x NUMBER;
l_output UTL_FILE.FILE_TYPE;
BEGIN
-- define output directory
l_output := UTL_FILE.FOPEN ('CWFSTORE_RESPONCE_XML', 'extract5001.txt', 'wb', 32760);
vstart := 1;
bytelen := 32000;
--select bob locator
FOR rec IN
(SELECT (response_file) vblob
FROM clarity_response_log
WHERE TRUNC(date_crated) = '08-oct-10'
)
LOOP
--get length of the blob
LEN := DBMS_LOB.getlength (rec.vblob);
DBMS_OUTPUT.PUT_LINE (LEN);
x := LEN;
-- if small enough for a single write
IF LEN < 32760 THEN
UTL_FILE.put_raw (l_output, rec.vblob);
UTL_FILE.PUT_LINE(l_output, CHR(13));
UTL_FILE.FFLUSH (l_output);
ELSE -- write in pieces
vstart := 1;
WHILE vstart < LEN
AND bytelen > 0
LOOP
DBMS_LOB.READ (rec.vblob, bytelen, vstart, my_vr);
UTL_FILE.put_raw (l_output, my_vr);
UTL_FILE.PUT_LINE(l_output,CHR(13));
UTL_FILE.FFLUSH (l_output);
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
END LOOP;
END IF;
END LOOP;
UTL_FILE.FCLOSE (l_output);
END;
My requirement goes something like this on the usage of
UTL_FILE.PUT_LINE(l_output, CHR(13));
do we need to use this code above or below the
UTL_FILE.FFLUSH (l_output);
moreover can we replace
UTL_FILE.PUT_LINE(l_output, CHR(13);
with
UTL_FILE.PUT_LINE(l_output, CHR(13)||chr(10));
Please advise