将文档加载到clob字段

SQL> create directory lob_src as 'D:\walog';


目录已创建。


SQL> create sequence doc_seq;


序列已创建。


SQL> create table txt_docs(doc_num number,doc_nm varchar2(100),doc_clb clob,ins_ts timestamp);

表已创建。

declare
src_clb bfile;
dst_clb clob;
src_doc_nm varchar2(100) :='wa.query.log';
src_offset integer :=1;
dst_offset integer :=1;
lang_ctx integer := dbms_lob.default_lang_ctx;
warning_msg number;
begin
src_clb:=bfilename('LOB_SRC',src_doc_nm);
insert into txt_docs(doc_num,doc_nm,doc_clb,ins_ts) values(doc_seq.nextval,src_doc_nm,empty_clob(),systimestamp) returning doc_clb into dst_clb;
dbms_lob.open(src_clb,dbms_lob.lob_readonly);
dbms_lob.loadclobfromfile(dest_lob=>dst_clb,src_bfile=>src_clb,amount=>dbms_lob.lobmaxsize,dest_offset=>dst_offset,src_offset=>src_offset,bfile_csid=>dbms_lob.default_csid,lang_context=>lang_ctx,warning=>warning_msg);
dbms_lob.close(src_clb);
commit;
dbms_output.put_line('Wrote clob to table:'||src_doc_nm);
end;

select * from txt_docs;

2 wa.query.log<CLOB> 16-JUN-15 11.33.42.909000 AM

更新lob为空

update txt_docs set doc_clb=empty_bloc() where doc_num=2;