--------------------------------------------- -- Export file for user JHMCC -- -- Created by sysman on 2009-4-1, 13:49:27 -- --------------------------------------------- spool 123.log prompt prompt Creating procedure READ_BLOB_DOC prompt ================================ prompt CREATE OR REPLACE PROCEDURE JHMCC.Read_Blob_doc ( -- Author : wujing -- Created : 2008-2-28 -- Purpose : 结合 UTL_FILE与DBMS_LOB包,将clob字段内容导入文件 filename varchar2 --CREATE OR REPLACE DIRECTORY DOCS AS 'F:\MyOem' :必要条件,申明 DOCS 路径变量 ) as l_file UTL_FILE.FILE_TYPE; l_buffer VARCHAR2(32767); --缓存区,保证极限大 --l_buffer RAW(32767); l_amount BINARY_INTEGER:=1000; --每次读取的最大字节数,可自由调节,切忌接近32767 l_pos NUMBER:=1; l_Blob clob; l_Blob_len Number; BEGIN SELECT t.zlnr INTO l_Blob FROM zl_info_nr t where t.zlcode='2008122601764'; --2008122601764 --2008122516884 l_Blob_len:=dbms_lob.getlength(l_Blob); l_file:=UTL_FILE.FOPEN('DOCS',filename,'W'); -- R: 只读模式 -- A: 追加读写模式 -- W: 刷新读写模式 --UTL_FILE.GET_LINE(l_file,l_buffer); --dbms_output.put_line(l_buffer); utl_file.put_line(l_file,'jobs表导出数据'); WHILE l_pos<l_Blob_len LOOP DBMS_LOB.READ(l_Blob,l_amount,l_pos,l_buffer); UTL_FILE.put_line(l_file,l_buffer); UTL_FILE.FFLUSH(l_file); --UTL_FILE.PUT_RAW(l_file,l_buffer,TRUE); l_pos:=l_pos+l_amount; END LOOP; UTL_FILE.FCLOSE(l_file); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(substr(l_buffer,1,200));--捕捉出错的数据段 utl_file.put_line(l_file,'妈的,导入出错了'); IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF; --RAISE; END Read_Blob_doc;
说明:我同事吴靖的作品,本人都没怎么看懂,如果需要看懂请查阅UTL_FILE相关资料!