I think it must be something with your data. I just did this:
SQL> drop table t purge;
Table dropped.
SQL> create table t ( x clob );
Table created.
SQL>
SQL> declare
2 c clob;
3 begin
4 insert into t values ( empty_clob())
5 returning x into c;
6
7 c := q'{<?xml version='1.0' encoding='UTF8' ?>}<RESULTS>}';
8 loop
9 c := c || '<ROW><COLUMN NAME="EMPNO"><![CDATA[7369]]></COLUMN><COLUMN NAME="ENAME"><![CDATA[SMITH]]></COLUMN><COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN><COLUMN NAME="MGR"><![CDATA[7902]]></COLUMN></ROW>';
10 exit when length(c) > 90000;
11 end loop;
12 c := c || '</RESULTS>';
13
14 update t set x = c;
15 commit;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL> select length(x), x from t;
LENGTH(X) X
---------- --------------------------------------------------------------------------------
90190 <?xml version='1.0' encoding='UTF8' ?>}<RESULTS><ROW><COLUMN NAME="EMPNO"><![CD
1 row selected.
SQL>
SQL> DECLARE
2 Lv_cursor PLS_INTEGER;
3 Lv_file UTL_FILE.file_type;
4 P_SubstrVal VARCHAR2(4000) ;
5 lv_file_name VARCHAR2 (100):='testing.csv';
6 P_Directory VARCHAR2(101) :='TEMP';
7 crlf VARCHAR2(2) := chr(10)||chr(13);
8 l_columnValClob CLOB;
9 P_offset NUMBER :=1;
10 P_amount NUMBER := 3000;
11 l_clobLen NUMBER :=0;
12 BEGIN
13 Lv_cursor := DBMS_SQL.open_cursor;
14 SELECT x
15 INTO l_columnValClob
16 FROM t;
17
18 l_clobLen := DBMS_LOB.GETLENGTH(l_columnValClob);
19 Lv_file := utl_file.fopen(P_Directory,lv_file_name,'wb');
20 WHILE P_offset < l_clobLen
21 LOOP
22 P_SubstrVal := dbms_lob.substr(l_columnValClob,P_amount,P_offset);
23 utl_file.put_raw(Lv_file,utl_raw.cast_to_raw(P_SubstrVal));
24 P_offset:=P_offset+P_amount;
25 END LOOP;
26 UTL_FILE.fclose(Lv_file);
27 END ;
28 /
PL/SQL procedure successfully completed.
SQL>
SQL>
C:\temp>wc -l testing.csv
0 testing.csv
C:\temp>awk "{print length($0)}" testing.csv
90190
C:\temp>cat testing.csv
<?xml version='1.0' encoding='UTF8' ?>}<RESULTS><ROW><COLUMN NAME="EMPNO">...
So
- it is "zero" lines long (ie, there are not cr/lf in there).
- it is 90910 bytes, same as the clob
- the data looks correct.