Oracle中BLOB/CLOB相關操作的使用筆記

 

使用pl/sql讀取寫入blob對象

一、寫檔案到BLOB
SQL> create table iihero_blob(fid int primary key, fname varchar(32), f blob);

表已建立。

SQL> conn / as sysdba
已串連。
SQL> create or replace directory BLOBDIR as 'd:oraclefile';

目錄已建立。

SQL> grant read on directory BLOBDIR to test;

授權成功。

SQL> conn test/test
已串連。

SQL> create sequence s_iihero_seq
  2  start with 1
  3  increment by 1
  4  /

序列已建立。

CREATE OR REPLACE PROCEDURE iihero_load_blob (pfname VARCHAR2)
IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
   src_file := bfilename('BLOBDIR', pfname);

   INSERT INTO iihero_blob (fid,fname,f)
   VALUES (S_IIHERO_SEQ.Nextval,pfname,EMPTY_BLOB())
   RETURNING f INTO dst_file;

   SELECT f INTO dst_file
   FROM iihero_blob  WHERE 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);

  UPDATE iihero_blob  SET f = dst_file
  WHERE fname = pfname;

  dbms_lob.fileclose(src_file);
  commit;
END iihero_load_blob;
/

SQL> col segment_name for a30
SQL> conn / as sysdba
已串連。
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where ow
ner='TEST';

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
ST_COURSE                      TABLE                        .0625
TAB2                           TABLE                        .0625
IIHERO_BLOB                    TABLE                        .0625
SYS_LOB0000030667C00003$$      LOBSEGMENT                   .0625

已選擇34行。

SQL> conn test/test
已串連。
SQL>

4. 裝資料(寫資料到BLOB)

SQL> exec iihero_load_blob('ttt.txt');

PL/SQL 過程已成功完成。

SQL> select fid, fname, dbms_lob.getlength(f) from iihero_blob;

       FID FNAME                            DBMS_LOB.GETLENGTH(F)
---------- -------------------------------- ---------------------
         1 ttt.txt                                          28729
        
二、將BLOB中的資料讀到檔案
1. 確認有資料
SQL> select fid, fname, dbms_lob.getlength(f) from iihero_blob;

       FID FNAME                            DBMS_LOB.GETLENGTH(F)
---------- -------------------------------- ---------------------
         1 ttt.txt                                          28729

2. 建立目錄
SQL> conn / as sysdba
已串連。
SQL> create or replace directory BLOBDIR as 'd:oraclefile';
目錄已建立。
SQL> grant read,write on directory BLOBDIR to test;
授權成功。 

3.建立預存程序
conn test/test

CREATE OR REPLACE PROCEDURE iihero_dump_blob(piname varchar2,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 f  INTO l_blob  FROM iihero_blob  WHERE  FNAME = piname;
  l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
  --dbms_output.put_line(l_blob_len);
  --l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767);
  l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'w', 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;
/

SQL> host dir/b/s d:oraclefile
d:oraclefileexample.txt
d:oraclefile tt.txt