oracle新增blob,学习笔记:Oracle在sqlplus中操作更新插入blob和clob的大字段

天萃荷净

在Oracle sqlplus中操作blob和clob

--create directory

create directory ULTLOBDIR as 'd:'

--create table

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

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;

--查询是否成功

select dbms_lob.getlength(col1) from blobtest;

select dbms_lob.getlength(col1) from clobtest;

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle在sqlplus中操作更新插入blob和clob的大字段