Blob字段类型的使用实例

在开发过程中,有时候需要使用Blob字段来存储大对象, 下面是使用blob的操作的过程实例使用, 根据情况修改后使用. 需要相应的权限.

创建目录.

SQL> create directory lob_dir as 'd:/test';

--加载Blob

create or replace procedure load_blob
(
    p_name in varchar2,
    p_desc in varchar2
) as
    fbfile bfile;
    lgh_file BINARY_INTEGER;
    dest_file blob;
begin
    insert into test_blob values(1,p_name,p_desc,empty_blob());
    select a.pic into dest_file from test_blob a where a.id=1 and rownum=1 for update ;
    fbfile:=bfilename('LOB_DIR',p_name);
    dbms_lob.fileopen(fbfile,dbms_lob.file_readonly);
    lgh_file:=dbms_lob.getlength(fbfile);
    dbms_lob.loadfromfile(dest_file,fbfile,lgh_file);
    update test_blob t set t.pic=dest_file where t.id=1;
    dbms_lob.fileclose(fbfile);
    commit;
end ;
/

--提取Blob

CREATE OR REPLACE PROCEDURE extract_blob (poname varchar2) IS
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob      BLOB;
  l_blob_len  INTEGER;
BEGIN
  SELECT PIC
  INTO      l_blob
  FROM      test_blob
  WHERE  id=1 and rownum=1;
  l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
  l_file := UTL_FILE.FOPEN('LOB_DIR',poname,'wb', 32767);
  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
     l_pos := l_pos + l_amount;
   END LOOP;
    UTL_FILE.FCLOSE(l_file);
 EXCEPTION
   WHEN OTHERS THEN
     IF UTL_FILE.IS_OPEN(l_file) THEN
       UTL_FILE.FCLOSE(l_file);
      END IF;
     RAISE;
 END;
/

#  Dinya