BFILENAME加载图片示例

--创建测试表

CREATE TABLE my_blobs ( 
　　dname VARCHAR2(30), --目录名称
　　sname VARCHAR2(30), --目录描述 
　　fname VARCHAR2(30), -- 文件名称
　　fblob BLOB); --图片


--授权创建目录
SQL> conn / AS SYSDBA
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as SYS
SQL> GRANT CREATE ANY DIRECTORY TO SPATIAL ;

Grant succeeded

--创建目录
SQL> conn spatial/spatial
Connected.
SQL> CREATE OR REPLACE DIRECTORY spatial_dir AS '/home/oracle/spatial';


--创建加载图片存储
CREATE OR REPLACE PROCEDURE LOAD_BLOB(PDNAME IN VARCHAR2,
                                      PSNAME IN VARCHAR2,
                                      PFNAME IN VARCHAR2) IS
  SRC_FILE BFILE;
  DST_FILE BLOB;
  LGH_FILE BINARY_INTEGER;
BEGIN
  SRC_FILE := BFILENAME('SPATIAL_DIR', PFNAME);
  -- 插入一条空BLOB数据记录,返回BLOB字段
  INSERT INTO MY_BLOBS
    (DNAME, SNAME, FNAME, FBLOB)
  VALUES
    (PDNAME, PSNAME, PFNAME, EMPTY_BLOB())
  RETURNING FBLOB INTO DST_FILE;
  -- 锁住该记录
  SELECT FBLOB
    INTO DST_FILE
    FROM MY_BLOBS
   WHERE DNAME = PDNAME
     AND SNAME = PSNAME
     AND FNAME = PFNAME
     FOR UPDATE;
  -- 打开图片文件
  DBMS_LOB.FILEOPEN(SRC_FILE, DBMS_LOB.FILE_READONLY);
  -- 获取图片长度
  LGH_FILE := DBMS_LOB.GETLENGTH(SRC_FILE);
  -- 读取图片到目标文件
  DBMS_LOB.LOADFROMFILE(DST_FILE, SRC_FILE, LGH_FILE);
  -- 更新BLOB字段
  UPDATE MY_BLOBS
     SET FBLOB = DST_FILE
   WHERE DNAME = PDNAME
     AND SNAME = PSNAME
     AND FNAME = PFNAME;
  -- 关闭文件读取
  DBMS_LOB.FILECLOSE(SRC_FILE);
END LOAD_BLOB;




--测试加载图片
SQL> EXEC LOAD_BLOB('SPATIAL_DIR','ORACLE TIMESTEN','qq.png'); 
PL/SQL procedure successfully completed.




--从OS上检查BLOB大小
SQL> !
[oracle@xml-ora1 ~]$ ll /home/oracle/spatial/qq.png 
-rw-r--r-- 1 oracle oinstall 119700 Jun  1 09:31 /home/oracle/spatial/qq.png


--从Oracle检查BLOB大小


SQL> set serveroutput on
SQL> DECLARE
  2    A BLOB;
  3  BEGIN
  4    SELECT FBLOB INTO A FROM MY_BLOBS;
  5    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(A));
  6  END;
  7  /
119700


PL/SQL procedure successfully completed.