Tom在他的《EXPert one on one Oracle》给出了sqlplus中将文件load进BLOB或者CLOB字段的例子。这里保存一份以备急用。
create Directory utllobdir as 'd:'
create table bloBTest(col1 BLOB);create table clobtest(col1 cLOB);--insert BLOBdeclarea_blob BLOB;bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');begininsert into blobtest values (empty_blob())returning col1 into a_blob;dbms_lob.fileopen(bfile_name);dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));dbms_lob.fileclose(bfile_name);commit;end;--update BLOBdeclarea_blob BLOB;bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');beginupdate blobtest set col1=empty_blob() where rownum=1returning col1 into a_blob;dbms_lob.fileopen(bfile_name);dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));dbms_lob.fileclose(bfile_name);commit;end;--insert CLOBcreate table clobtest(col1 CLOB);declarea_clob CLOB;bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');begininsert into clobtest values (empty_clob())returning col1 into a_clob;dbms_lob.fileopen(bfile_name);dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));dbms_lob.fileclose(bfile_name);commit;end;--update CLOBdeclarea_clob CLOB;bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');beginupdate clobtest set col1=empty_clob() where rownum=1returning col1 into a_clob;dbms_lob.fileopen(bfile_name);dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));dbms_lob.fileclose(bfile_name);commit;end;
通过查看lob字段的长度判定是否插入成功
select dbms_lob.getlength(col1) from blobtest;select dbms_lob.getlength(col1) from clobtest;