oracle blob批量,oracle 批量插入photo(图片)到数据库blob

今天早上一来发现昨晚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, 显示正常！