[原]将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;  
posted @ 2011-04-30 00:41  killkill  阅读(8533)  评论(1编辑  收藏