将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 ;