今天早上一来发现昨晚kk给留言说试一下把图像导入oracle数据库,下面试验一把
因为文件名如果没有规律,不用开发语言实现有点麻烦,所以前规范化图像文件名,按序列重命名文件
下面实验开始,环境linux+oracle 10g r2
1,linux oracle 用户建一个文件夹,从windows client传几个图片过去,(文件夹权限读写,owner oracle)
2,规范文件名,小心有重名被替换而丢失
[oracle@aix imgs]$ ls
11.gif 12.gif 19.gif 1.gif 31.gif 8.gif
[oracle@aix imgs]$ i=100;for img in *.gif;do ((i++)); mv "$img" ${i}.gif;done
[oracle@aix imgs]$ ls
101.gif 102.gif 103.gif 104.gif 105.gif 106.gif
3,导入数据库
SQL> conn anbob/anbob
Connected.
SQL> create table testimg(id int,photo blob);
Table created.
SQL> create or replace directory imgpath as '/oracle/imgs';
create or replace directory imgpath as '/oracle/imgs'
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn system/oracle
Connected.
SQL> create or replace directory imgpath as '/oracle/imgs';
Directory created.
SQL> grant read,write on directory imgpath to anbob;
Grant succeeded.
SQL> conn anbob/anbob
Connected.
SQL> declare
2 l_blob blob;
3 l_bfile bfile;
4 begin
5 for i in 101..106 loop
6 insert into testimg(id,photo)
7 values(1,empty_blob())
8 returning photo into l_blob;
9 l_bfile :=bfilename('IMGPATH',i||'.gif');
10 dbms_lob.fileopen(l_bfile);
11 dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
12 dbms_lob.fileclose(l_bfile);
13 end loop;
14 commit;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL> select id,dbms_lob.getlength(photo) blen from testimg;
ID BLEN
---------- ----------
1 700
1 6371
1 699
1 552
1 493
1 659
6 rows selected.
4,查看数据准确性,用toad 查询,双击blob 列,弹出小窗口,点一个红色按钮,save file and open, 显示正常!
打赏
微信扫一扫,打赏作者吧~