有时会有这种需求,记录一下
declare
l_directory varchar(200) := '__dir__';
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER:=32767;
l_pos NUMBER:=1;
l_Blob Blob;
l_Blob_len Number;
l_lob_id number;
CURSOR cur is
select __id__ from __table_name__;
BEGIN
open cur;
loop
fetch cur into l_lob_id;
EXIT when cur%NOTFOUND;
select __blob_fild__ into l_Blob from __table_name__ where __id__=l_lob_id ;
l_file:=UTL_FILE.FOPEN(l_directory,'adapter_blob.'||to_char(l_lob_id)||'.bin','wb',32767);
l_Blob_len:=dbms_lob.getlength(l_Blob);
l_pos := 1;
WHILE l_pos<l_Blob_len LOOP
DBMS_LOB.READ(l_Blob,l_amount,l_pos,l_buffer);
UTL_FILE.PUT_RAW(l_file,l_buffer,TRUE);
l_pos:=l_pos+l_amount;
end loop;
utl_file.fclose(l_file);
end loop;
exception
when others then
if utl_file.is_open(l_file) then
utl_file.fclose(l_file);
end if;
END;