oracle blob的操作。

這文章來自網上﹐并做了修改﹐現已找不到源頭了﹐關鍵部分是通過global temporary table 與 db link來處理blob字段.此測試在同一機器back帳號下面做的,oracle為10.2.0.3

--在測試schema上建表﹐
create table picture(
          picture_name varchar2(30),
          picture_blob blob,
          picture_locator bfile);

create table picture2(
          picture_name varchar2(30),
          picture_blob blob,
          );

create global temporary table tmp_pic(picture_blob blob) on commit delete rows;
 ----on commit preserve rows;

在測試schema下面建立本機本帳號的db link 做測試﹕
 create database link test10g.com.cn connect to back identified by "back" using 'test10g.com.cn';

--以sys帳號登錄建立目錄﹐然后授權read,write權限給測試帳號
create directory picture_dir as '/oradata';
grant read,write on directory picture_dir to back;

insert into picture (picture_name,picture_blob,picture_locator) values('pic1',empty_blob());
insert into picture2 (picture_name,picture_blob,picture_locator) values('pic1',empty_blob());
commit;


---把/oradata/10g.sh腳本存入picture表---
declare
  l_pic_locator bfile;
  l_pic_blob blob;
  l_dir varchar2(200);
  l_filename varchar2(200);
  amount int;
BEGIN
  select picture_locator,picture_blob into l_pic_locator,l_pic_blob from picture where PICTURE_NAME='pic1' for update;
  -- for update 是因為LOADFROMFILE的時候﹐那一行必須是鎖定的﹐否則LOADFROMFILE時報錯無法鎖定。

  -- 1。判斷文件是否存在
  IF DBMS_LOB.FILEEXISTS(l_pic_locator)=1 then
     dbms_output.put_line('!!! The file exists!');
 
  -- 2. 判斷文件是否已被打開　
  if dbms_lob.fileisopen(l_pic_locator)=0 THEN
     DBMS_OUTPUT.PUT_LINE(' THE FILE IS NOT OPEN, TO OPEN THE FILE...');
 
  -- 3. 打開文件　
     DBMS_LOB.FILEOPEN(l_pic_locator);
     DBMS_OUTPUT.PUT_LINE('!!! THE FILE IS OPENED!');
 
  -- 4. 可以獲得關於文件的一些資訊，比如文件名，大小等
     dbms_output.put_line('The Length of the file is:'||to_char(dbms_lob.getlength(l_pic_locator)));
     dbms_lob.filegetname(l_pic_locator,l_dir,l_filename);
     DBMS_OUTPUT.PUT_LINE('The opened file name is :'||l_dir||'/'||l_filename);
 
  -- 5. 將外部文件裝入Blob字段
     amount :=dbms_lob.getlength(l_pic_locator);
     DBMS_LOB.LOADFROMFILE(l_pic_blob,l_pic_locator,amount,1,1);
     dbms_output.put_line('The file is loaded into database!');
  END IF;
     DBMS_OUTPUT.PUT_LINE('To close the file');
 
  --6. 每一個fileopen必須有一個fileclose與之對應，所以關閉文件
  IF DBMS_LOB.FILEISOPEN(l_pic_locator)=1 THEN
     DBMS_LOB.FILECLOSE(l_pic_locator);
     DBMS_OUTPUT.PUT_LINE('The file is closed!');
  END IF;
 END IF;
  -- 7. 還必須在exception中保證文件關閉文件　
 exception
    when others then
        IF DBMS_LOB.FILEISOPEN(l_pic_locator)=1 THEN
           DBMS_LOB.FILECLOSE(l_pic_locator);
           DBMS_OUTPUT.PUT_LINE
                ('An exception happens,the file is closed!');
        else
           dbms_output.put_line('An exception happens!');
       end if;
END;




---輸出測試文件看是否成功 ---------
declare
 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;
 t_blob blob;
BEGIN
 ---測試db link來處理blob.
 insert into tmp_pic select picture_blob from [e-mail] where picture_name='pic1';
 select picture_blob into t_blob from tmp_pic;
 update picture2 set picture_blob=t_blob where picture_name='pic1';
 commit;

--輸出前面導入到資料庫的文件,測試是否成功。 
 select PICTURE_BLOB  into l_blob from picture2 where picture_name='pic1';
 l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
 l_file := UTL_FILE.FOPEN('PICTURE_DIR','10g_test.sh','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;