访问clob类型的数据及bfilename的案例

–1，建立clob类型的表
create table lob_ex1 (
id number(6) primary key,
name varchar2(10),
resume clob
);

–2,初始化clob列
/*
empty_clob() 与 null 不同，前者为列分配了定位符
*/
insert into lob_ex1 values(1,’王瑶’,empty_clob());
insert into lob_ex1 values(2,’李思玉’,empty_clob());
commit;

–3,更新clob的数据

declare
v_lob clob ;
v_acount number(10);
v_offset int ;
v_context varchar2(200) ;
begin
–for update 目的为了更新表中数据
select resume into v_lob from lob_ex1 where id = 2 for update;
v_offset := dbms_lob.getlength(v_lob)+1;
v_context := ‘&con’;
v_acount := length(v_context);
dbms_lob.write(v_lob,v_acount,v_offset,v_context);
commit;
end;

–4，读取clob列的数据

declare
v_lob clob;
buffer varchar2(200);
start_location int := 1;
v_acount int;
begin
select resume into v_lob from lob_ex1 where id =1;
v_acount := dbms_lob.getlength(v_lob);
dbms_lob.read(v_lob,v_acount,start_location,buffer);
dbms_output.put_line(buffer);
end;

–5,将文本文件内容写到clob中去

create or replace directory dir_data as ‘F:\’; –f盘下

declare
lobloc clob ;
fileloc bfile;
v_acount int ;
src_offset int :=1;
dest_offset int := 1;
csid int := 0;
lc int := 0;
warning int ;
begin
fileloc := bfilename (‘DIR_DATA’,’a.txt’);
dbms_lob.fileopen(fileloc,0);–0代表只读的意思
v_acount := dbms_lob.getlength(fileloc);
select resume into lobloc from lob_ex1 where id = 1 for update;
dbms_lob.loadclobfromfile(lobloc,fileloc,v_acount,dest_offset,src_offset,csid,lc,warning);
dbms_lob.fileclose(fileloc);
commit;
end;

select * from lob_ex1;
–5,将clob内容写到bfile文件中去

declare
lobloc clob;
v_amount int;
offset int := 1;
buffer varchar2(200);
handle Utl_File.file_type;
begin
select resume into lobloc from lob_ex1 where id = 1;
v_amount:= dbms_lob.getlength(lobloc);
dbms_lob.read(lobloc,v_amount,offset,buffer);
handle := utl_file.fopen(‘DIR_DATA’,’a.txt’,’w’,2000);
utl_file.put_line(handle,buffer );
utl_file.fclose(handle);
end;

---

create or replace directory dir_data as ‘F:\’;

–放数据
declare
handle utl_file.file_type;
buffer varchar2(200);
begin
handle:=utl_file.fopen(‘DIR_DATA’,’a.txt’,’w’);
utl_file.put_line(handle,’我是猪八戒…….’ );
utl_file.put_line(handle,’我是孙悟空……’ );
utl_file.fclose(handle);
end;

–读数据
declare
handle utl_file.file_type;
buffer varchar2(200);
begin
handle:=utl_file.fopen(‘DIR_DATA’,’a.txt’,’R’);
utl_file.get_line(handle,buffer );
dbms_output.put_line(buffer);
utl_file.get_line(handle,buffer );
dbms_output.put_line(buffer);
utl_file.fclose(handle);
end;

–查看目录表–dba_directories
select * from dba_directories;