ANBOB™

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

打赏

微信扫一扫，打赏作者吧～