[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.