将Oracle 中的blob导出到文件中

有时会有这种需求，记录一下

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;

[原]将Oracle 中的blob导出到文件中 - killkill - 博客园