LOB数据导出一例

lob数据导出代码：

DECLARE
  V_BLOB      BLOB;
  V_CLOB      CLOB;
  V_AMOUNT    NUMBER := 32767;
  V_POSITION  NUMBER;
  V_RAW_BUF   RAW(32767);
  V_CHR_BUF   VARCHAR2(32767);
  V_CHUNKSIZE NUMBER;

  V_OUT_FILE UTL_FILE.FILE_TYPE;
BEGIN
  --get lobs
  SELECT CLOB_COL, BLOB_COL
    INTO V_CLOB, V_BLOB
    FROM LOB_TAB
   WHERE ID = 1
     AND ROWNUM = 1;

  /**************************************************************************/
  --BLOB类型数据导出
  --open source file
  V_OUT_FILE := UTL_FILE.FOPEN(LOCATION     => 'ORCL_DIR',
                               FILENAME     => 'blob_file.out',
                               OPEN_MODE    => 'wb',
                               MAX_LINESIZE => 32767);

  --initialization amount and position
  V_CHUNKSIZE := DBMS_LOB.GETCHUNKSIZE(V_BLOB);
  IF V_CHUNKSIZE <= 32767 THEN
    V_AMOUNT := FLOOR(32767 / V_CHUNKSIZE) * V_CHUNKSIZE;
  END IF;
  V_POSITION := 1;

  --read from V_blob and write to file
  FOR I IN 1 .. CEIL(DBMS_LOB.GETLENGTH(V_BLOB) / V_AMOUNT) LOOP
    DBMS_LOB.READ(V_BLOB, V_AMOUNT, V_POSITION, V_RAW_BUF);
    V_POSITION := V_POSITION + V_AMOUNT;
  
    UTL_FILE.PUT_RAW(FILE      => V_OUT_FILE,
                     BUFFER    => V_RAW_BUF,
                     AUTOFLUSH => TRUE);
    UTL_FILE.FFLUSH(FILE => V_OUT_FILE);
  END LOOP;

  UTL_FILE.FFLUSH(FILE => V_OUT_FILE);
  UTL_FILE.FCLOSE(FILE => V_OUT_FILE);

  /*******************************************************************************/
  --CLOB类型数据导出
  --open source file
  V_OUT_FILE := UTL_FILE.FOPEN(LOCATION     => 'ORCL_DIR',
                               FILENAME     => 'clob_file.out',
                               OPEN_MODE    => 'w',
                               MAX_LINESIZE => 32767);

  --initialization amount and position
  V_CHUNKSIZE := DBMS_LOB.GETCHUNKSIZE(V_CLOB);
  IF V_CHUNKSIZE <= 32767 THEN
    V_AMOUNT := FLOOR(32767 / V_CHUNKSIZE) * V_CHUNKSIZE;
  END IF;
  V_POSITION := 1;

  --read from V_clob and write to file
  FOR I IN 1 .. CEIL(DBMS_LOB.GETLENGTH(V_CLOB) / V_AMOUNT) LOOP
    DBMS_LOB.READ(V_CLOB, V_AMOUNT, V_POSITION, V_CHR_BUF);
    V_POSITION := V_POSITION + V_AMOUNT;
  
    UTL_FILE.PUT(FILE => V_OUT_FILE, BUFFER => V_CHR_BUF);
    UTL_FILE.FFLUSH(FILE => V_OUT_FILE);
  END LOOP;

  UTL_FILE.FFLUSH(FILE => V_OUT_FILE);
  UTL_FILE.FCLOSE(FILE => V_OUT_FILE);

END;
/

说明：只是一个成功导出的示例，许多细节还需以后详细研究；在该例子中，CLOB字段数据不宜过大，否则会报ORA-06502的错误，具体原因还未深究。