oracle Blob保存方式,oracle 存储过程操作blob

一、授权用户及创建目录

SQL> grant CREATE ANY DIRECTORY to user;

Grant succeeded.

SQL> conn user/password

Connected.

SQL> create or replace directory BLOBDIR as '/home/oracle/picDir';

Directory created.

二、导出文件procedure

create or replace procedure photo_dump(IDENTITYID in varchar2,

filename in 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 key_value

INTO l_blob

FROM blob_map_t

WHERE key_id = IDENTITYID;

l_blob_len := DBMS_LOB.GETLENGTH(l_blob);

l_file := UTL_FILE.FOPEN('BLOBDIR',

filename,

'wb',

32767);

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 OTHERS THEN

dbms_output.put_line(SQLERRM);

IF UTL_FILE.IS_OPEN(l_file) THEN

UTL_FILE.FCLOSE(l_file);

END IF;

RAISE;

end photo_dump;

三、调用导出procedure

create or replace procedure out_put_photo is

IDENTITYID varchar2(300);

FILENAME varchar2(300);

cursor cur is

select key_id, key_name

from blob_map_t;

cur_result cur%rowtype;

begin

if cur%isopen = false then

open cur;

end if;

loop

fetch cur

into cur_result;

exit when cur%notfound;

IDENTITYID := cur_result.key_id;

FILENAME := cur_result.key_name;

photo_dump(identityid => IDENTITYID, filename => FILENAME || '.jpg');

end loop;

close cur;

end out_put_photo;

四、导出执行

begin

-- Call the procedure

out_put_photo;

end;