BLOB的存储和读取
时间：2010-08-05 来源：rushmeplz
创建测试表
SQL> create table alice.user_stats(
2 id number primary key,
3 username varchar2(4000),
4 app blob)
5 partition by range(id)
6 (partition part01 values less than(50),
7 partition part02 values less than(maxvalue))
8 / 创建directory SQL> create directory bfile as '/oracle'; Directory created. SQL> grant read,write on directory bfile to alice; Grant succeeded. 存储blob SQL> declare sfile bfile:=bfilename('BFILE','dotnetfx.exe');
2 length_file int :=dbms_lob.getlength(sfile); --得到文件长度
3 dfile blob; blob指针
4 begin
5 insert into alice.user_stats(id,app) values(10,empty_blob());
6 select app into dfile from alice.user_stats; --记录blob指针
7 dbms_lob.fileopen(sfile,dbms_lob.file_readonly); --以只读方式打开bfile模式的文件
8 dbms_lob.loadfromfile(dfile,sfile,length_file); --开始加载指定长度的文件到指针位置
9 dbms_lob.fileclose(sfile); --关闭
10 commit;
11 end;
12 / 读取blob SQL> declare l_file utl_file.file_type;
2 l_buffer raw(32767);
3 l_amount binary_integer :=32767;
4 l_pos int :=1;
5 l_blob blob;
6 l_blob_length int;
7 begin
8 select app into l_blob from alice.user_stats where id=10;
9 l_blob_length:=dbms_lob.getlength(l_blob);
10 l_file :=utl_file.fopen('BFILE','dot.exe','wb',32767); --创建文件
11 while l_pos<l_blob_length loop
12 dbms_lob.read(l_blob,l_amount,l_pos,l_buffer); --循环读取blob到buffer
13
14 utl_file.put_raw(l_file,l_buffer,true); --刷新缓存到文件
15 l_pos:=l_pos+l_amount;
16 end loop;
17 utl_file.fclose(l_file); --关闭文件
18 end;
19 /
2 id number primary key,
3 username varchar2(4000),
4 app blob)
5 partition by range(id)
6 (partition part01 values less than(50),
7 partition part02 values less than(maxvalue))
8 / 创建directory SQL> create directory bfile as '/oracle'; Directory created. SQL> grant read,write on directory bfile to alice; Grant succeeded. 存储blob SQL> declare sfile bfile:=bfilename('BFILE','dotnetfx.exe');
2 length_file int :=dbms_lob.getlength(sfile); --得到文件长度
3 dfile blob; blob指针
4 begin
5 insert into alice.user_stats(id,app) values(10,empty_blob());
6 select app into dfile from alice.user_stats; --记录blob指针
7 dbms_lob.fileopen(sfile,dbms_lob.file_readonly); --以只读方式打开bfile模式的文件
8 dbms_lob.loadfromfile(dfile,sfile,length_file); --开始加载指定长度的文件到指针位置
9 dbms_lob.fileclose(sfile); --关闭
10 commit;
11 end;
12 / 读取blob SQL> declare l_file utl_file.file_type;
2 l_buffer raw(32767);
3 l_amount binary_integer :=32767;
4 l_pos int :=1;
5 l_blob blob;
6 l_blob_length int;
7 begin
8 select app into l_blob from alice.user_stats where id=10;
9 l_blob_length:=dbms_lob.getlength(l_blob);
10 l_file :=utl_file.fopen('BFILE','dot.exe','wb',32767); --创建文件
11 while l_pos<l_blob_length loop
12 dbms_lob.read(l_blob,l_amount,l_pos,l_buffer); --循环读取blob到buffer
13
14 utl_file.put_raw(l_file,l_buffer,true); --刷新缓存到文件
15 l_pos:=l_pos+l_amount;
16 end loop;
17 utl_file.fclose(l_file); --关闭文件
18 end;
19 /
相关阅读 更多 +
