1.插入一条blob数据

SQL> create directory ULTLOBDIR as '/home/oracle';
Directory created.
SQL> create table blobtest(col1 BLOB);
Table created.
SQL> declare
a_blob BLOB;
  2    3  bfile_name BFILE := BFILENAME('ULTLOBDIR','tt.txt.bak');
  4  begin
  5  insert into blobtest values (empty_blob())
  6  returning col1 into a_blob;
  7  dbms_lob.fileopen(bfile_name);
  8  dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
  9  dbms_lob.fileclose(bfile_name);
 10  commit;
 11  end;
 12  /
PL/SQL procedure successfully completed.
SQL> select dbms_lob.getlength(col1) from blobtest;
DBMS_LOB.GETLENGTH(COL1)
------------------------
                    4829
SQL> !pwd
/home/oracle
SQL> !ls -l tt.txt.bak
-rw-r--r-- 1 oracle oinstall 4829 03-19 17:26 tt.txt.bak

2.创建存储过程

CREATE OR REPLACE PROCEDURE RETRIEVE_LOB_TO_FILE(TEMP_BLOB IN BLOB,
                                                 FILE_PATH IN VARCHAR2,
                                                 FILE_NAME IN VARCHAR2) IS
  DATA_BUFFER   RAW(32767);
  POSITION      INTEGER := 1;
  FILEHANDLE    UTL_FILE.FILE_TYPE;
  ERROR_NUMBER  NUMBER;
  ERROR_MESSAGE VARCHAR2(100);
  BLOB_LENGTH   INTEGER;
  CHUNK_SIZE    BINARY_INTEGER := 32767;
BEGIN
  BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB);
  FILEHANDLE  := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024);
  WHILE POSITION < BLOB_LENGTH LOOP
    DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER);
    UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER);
    POSITION    := POSITION + CHUNK_SIZE;
    DATA_BUFFER := NULL;
  END LOOP;
  UTL_FILE.FCLOSE(FILEHANDLE);
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      ERROR_NUMBER  := SQLCODE;
      ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100);
      DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER);
      DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE);
      UTL_FILE.FCLOSE_ALL;
    END;
END;
/

3.测试读取blob到系统

SQL> declare
  2  tmp_blob blob default empty_blob();
  3  begin
  4  dbms_lob.createtemporary(tmp_blob, true);
  5  select col1 into tmp_blob from blobtest;
  6  retrieve_lob_to_file (tmp_blob, 'ULTLOBDIR','xifenfei.txt');
  7  end;
  8  /
PL/SQL procedure successfully completed.
SQL> !pwd
/home/oracle
SQL> !ls -l xifenfei.txt
-rw-r--r-- 1 oracle oinstall 4829 03-20 23:44 xifenfei.txt