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的错误,具体原因还未深究。