导出oracle大字段BLOB,CLOB

create or replace procedure sp_lob_exp as
begin
declare
v_file_1 UTL_FILE.FILE_TYPE;
v_id varchar2(200);
v_b_lob_1 BLOB;
v_c_lob_1 CLOB;
v_len number;
v_pos number:=1;
v_amount BINARY_INTEGER:=32767;
v_data_amount BINARY_INTEGER:=32767;
v_buffer RAW(32767);
v_data_buffer varchar2(4000);
begin
for rec in
(
select xx as id from xx
)
LOOP
v_id:=rec.id;
v_b_lob_1:=rec.xx;
v_pos:=1;
v_len:=DBMS_LOB.getlength(v_b_lob_1);
v_file_1:=UTL_FILE.FOPEN(‘EXP_UTL_FILE目录’,‘文件名’,‘wb’,32767);
WHILE v_pos<v_len LOOP
DBMS_LOB.read(v_b_lob_1,v_amount,v_pos,v_buffer);
UTL_FILE.put_raw(v_file_1,v_buffer,TRUE);
v_pos:=v_pos+v_amount;
END LOOP;
UTL_FILE.fclose(v_file_1);

END LOOP;

CLOB wb 改 w put_raw 改 put

记得创建目录
create or repalace directory EXP_UTL_FILE as ‘/oracle/home/exp’;