Oracle使用存储过程下载Blob大对象

1. 创建存储过程 copy_blob_data_to_file

CREATE OR REPLACE PROCEDURE copy_blob_data_to_file(
p_blob_id INTEGER,
p_directory VARCHAR2
) AS
v_src_blob BLOB;
v_src_file_name VARCHAR2(20);
v_file UTL_FILE.FILE_TYPE;
v_offset INTEGER := 1;
v_amount INTEGER := 32767;
v_binary_buffer RAW(32767);
BEGIN
-- get the LOB locator of the BLOB
SELECT E.FJ,E.FJDZ  --从表中查询sjid为p_blob_id的大对象及其文件名
INTO v_src_blob,v_src_file_name
FROM EVENT E
WHERE E.SJID = p_blob_id;
-- open the file for writing of bytes (up to v_amount bytes at a time)
v_file := UTL_FILE.FOPEN(p_directory, v_src_file_name, 'wb', v_amount);
-- copy the data from v_src_blob to the file
LOOP
BEGIN
-- read characters from v_src_blob into v_binary_buffer
DBMS_LOB.READ(v_src_blob, v_amount, v_offset, v_binary_buffer);
-- copy the binary data from v_binary_buffer to the file
UTL_FILE.PUT_RAW(v_file, v_binary_buffer);
-- add v_amount to v_offset
v_offset := v_offset + v_amount;
EXCEPTION
-- when there is no more data in the file then exit
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
-- flush any remaining data to the file
UTL_FILE.FFLUSH(v_file);
-- close the file
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('Copy successfully completed.');
END copy_blob_data_to_file;

2. 调用存储过程copy_blob_data_to_file()

call copy_blob_data_to_file(20,’E:\exp’);

3. 调用过程中如果遇到如下错误

ORA-29280: invalid directory path
路径名不存在

4. 解决方法：

utl为空，设置utl_file_dir值

重启ORACLE服务,验证

5. 结束

现在utl_file_dir被赋值E:\exp，重新调用存储过程copy_blob_data_to_file()，执行成功，大对象BLOB从数据库中下载到本地文件。