在sqlplus中操作blob和clob_sqlplus 写文件 clob字段-优快云博客

本文介绍如何使用Oracle SQL*Plus将本地文件加载到数据库的BLOB或CLOB字段中，包括创建目录、表及插入和更新操作，并验证加载是否成功。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');
begin
insert 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 BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');
begin
update blobtest set col1=empty_blob() where rownum=1
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;

--insert CLOB
create table clobtest(col1 CLOB);

declare
a_clob CLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');
begin
insert 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 CLOB
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');
begin
update clobtest set col1=empty_clob() where rownum=1
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;

　　通过查看lob字段的长度判断是否插入成功


select dbms_lob.getlength(col1) from blobtest;
select dbms_lob.getlength(col1) from clobtest;