| |
【赛迪网－it技术报道】使用pl/sql从数据库中读取blob对象:
style="text-indent:2em">1.首先，确认现有对象
sql> col fdesc for a30
sql> select fid,fname,fdesc from eygle_blob;
fid fname fdesc
------------------------ ------------------------------
1 shaolin.jpg 少林寺-康熙手书
2 daoying.jpg 倒映
|
2.创建存储directory
sql> connect / as sysdba
connected.
sql> create or replace directory blobdir as 'd:oradatapic';
directory created.
sql>
sql> grant read,write on directory blobdir to eygle;
grant succeeded.
sql>
|
3.创建存储过程
sql> connect eygle/eygle
connected.
sql>
sql> create or replace procedure eygle_dump_blob (piname varchar2,poname varchar2) is
2 l_file utl_file.file_type;
3 l_buffer raw(32767);
4 l_amount binary_integer := 32767;
5 l_pos integer := 1;
6 l_blob blob;
7 l_blob_len integer;
8 begin
9 select fpic
10 into l_blob
11 from eygle_blob
12 where fname = piname;
13
14 l_blob_len := dbms_lob.getlength(l_blob);
15 l_file := utl_file.fopen('blobdir',poname,'wb', 32767);
16
17 while l_pos < l_blob_len loop
18 dbms_lob.read (l_blob, l_amount, l_pos, l_buffer);
19 utl_file.put_raw(l_file, l_buffer, true);
20 l_pos := l_pos + l_amount;
21 end loop;
22
23 utl_file.fclose(l_file);
24
25 exception
26 when others then
27 if utl_file.is_open(l_file) then
28 utl_file.fclose(l_file);
29 end if;
30 raise;
31 end;
32 /
procedure created.
|
4.最后取出数据
sql> host ls -l d:oradatapic
total 7618
-rwxrwxrwa 1 gqgai none 2131553 apr 19 10:12 daoying.jpg
-rwxrwxrwa 1 gqgai none 1768198 apr 19 10:12 shaolin.jpg
sql> exec eygle_dump_blob('shaolin.jpg','01.jpg')
pl/sql procedure successfully completed.
sql> host ls -l d:oradatapic
total 11072
-rwxrwxrwa 1 administrators system 1768198 apr 26 07:16 01.jpg
-rwxrwxrwa 1 gqgai none 2131553 apr 19 10:12 daoying.jpg
-rwxrwxrwa 1 gqgai none 1768198 apr 19 10:12 shaolin.jpg
sql>
sql> exec eygle_dump_blob('daoying.jpg','02.jpg')
pl/sql procedure successfully completed.
sql> host ls -l d:oradatapic
total 15236
-rwxrwxrwa 1 administrators system 1768198 apr 26 07:16 01.jpg
-rwxrwxrwa 1 administrators system 2131553 apr 26 07:19 02.jpg
-rwxrwxrwa 1 gqgai none 2131553 apr 19 10:12 daoying.jpg
-rwxrwxrwa
|
|
|
