oracle blob批量导出,如何在Oracle中将Blob存储的图片批量导出到图像文件

可将Oracle中Blob字段存储的图片批量导出,方便查看。

示例为Windows环境下的测试,Linux中将目录创建命令修改即可。

--1、以管理员用户登陆设置可操作目录

--CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

create or replace directory BLOBDIR as 'D:\PIC';--该文件夹必须已存在

grant read,write on directory BLOBDIR to test;

GRANT EXECUTE ON utl_file TO test;

select * from ALL_DIRECTORIES;

--select * from picture

--2、普通用户登陆,编写存储过程

CREATE OR REPLACE PROCEDURE GET_PIC_BLOB(i_xh VARCHAR2) IS

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

BEGIN

select pdata into l_blob from picture where plen=i_xh;

l_blob_len := DBMS_LOB.GETLENGTH(l_blob);

l_file := UTL_FILE.FOPEN('BLOBDIR', i_xh || '.jpg', 'WB', l_blob_len);

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);

UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);

l_pos := l_pos + l_amount;

END LOOP;

UTL_FILE.FCLOSE(l_file);

EXCEPTION

--WHEN NO_DATA_FOUND THEN

--DBMS_OUTPUT.put_line('no data : ' || i_xh);

WHEN OTHERS THEN

IF UTL_FILE.IS_OPEN(l_file) THEN

UTL_FILE.FCLOSE(l_file);

RAISE;

END IF;

END GET_PIC_BLOB;

--3、编写PL/SQL 块,循环执行该存储过程

declare

cursor cur is

select di

stinct(plen) plen from picture where rownum <= 5000 ;

begin

for rec in cur loop

GET_PIC_BLOB(rec.plen);

end loop;

end;