[Oracle]CLOB输出到FILE

CLOB输出到FILE
1.循环读出，但不能超出32K（这个是utl_file.put的限制，而不是dbms_lob.read的）。
--amount BINARY_INTEGER :=1;
--data_buffer VARCHAR2 (1);
length_count := dbms_lob.getlength(temp_blob);
WHILE length_count > 0 LOOP
dbms_lob.read (temp_blob, amount, position, data_buffer);
utl_file.put (filehandle, data_buffer);
position := position + 1;
length_count := length_count - 1;
data_buffer := null;
END LOOP;
 
2.搜索读出，但两个之间不能超出32K，即一行不能超出32K或data_buffer 的容量。
--amount BINARY_INTEGER ;
--data_buffer VARCHAR2 (4000);
--pattern  VARCHAR2(1);
pattern := chr(10);
amount := dbms_lob.instr(temp_blob, pattern,1,1)-1;
WHILE amount > 0 LOOP
dbms_lob.read (temp_blob,amount , position, data_buffer);
utl_file.put_line (filehandle, data_buffer);
position := dbms_lob.instr(temp_blob, pattern,position,1)+1;
amount := dbms_lob.instr(temp_blob,pattern,position,1)-position;
data_buffer := null;
END LOOP;
 
对LOB操作，不建议使用 dbms_lob.substr，因为有限制32k.