create or replace directory TEMP_DIR as 'D:TEMP';
set serveroutput on
DECLARE
v_id INT;
v_blob BLOB;
v_bfile BFILE;
v_desc INTEGER := 1;
v_src INTEGER := 1;
BEGIN
SELECT MIN(id)
INTO v_id
FROM test_lob
WHERE colblob IS NULL;
DBMS_LOB.CREATETEMPORARY(v_blob,TRUE);
v_bfile := BFILENAME('TEMP_DIR', 'DSC00279.JPG');
IF dbms_lob.fileexists(v_bfile) = 1 THEN
BEGIN
dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
dbms_lob.loadblobfromfile(v_blob, --dest_lob IN OUT NOCOPY BLOB,
v_bfile, --src_bfile IN BFILE,
DBMS_LOB.LOBMAXSIZE,
v_desc,v_src);
dbms_lob.fileclose(v_bfile);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_lob.fileclose(v_bfile);
dbms_lob.freetemporary(v_blob);
RETURN;
END;
UPDATE test_lob
SET colblob=v_blob
WHERE ID=v_id;
COMMIT;
dbms_lob.freetemporary(v_blob);
ELSE
dbms_output.put_line('Physical file does not exist.');
END IF;
END;
/
--将Blob的图像写回到磁盘上,使用UTL_FILE.PUT_RAW来实现
--UTL_FILE将文件作为text方式处理,所以生成出来的文件需要用ue之类的软件作一下格式化,否则无法正常使用Acdsee等看图软件看到图片
DECLARE
v_id INT := 1;
v_blob BLOB;
v_file UTL_FILE.FILE_TYPE;
buffer RAW(32767);
amt BINARY_INTEGER := 32767;
pos INTEGER := 1;
v_bloblen INTEGER;
BEGIN
SELECT colblob INTO v_blob
FROM test_lob
WHERE ID = v_id AND colblob IS NOT NULL;
v_file := UTL_FILE.FOPEN('D:TEMP','TEST.JPG','w',32767);
v_bloblen := DBMS_LOB.GETLENGTH (v_blob);
WHILE pos <= v_bloblen LOOP
dbms_lob.read (v_blob, amt, pos, buffer);
utl_file.put_raw(v_file,buffer,TRUE);
pos := pos + amt;
END LOOP;
UTL_FILE.FCLOSE(v_file);
END;
/
（本文已被浏览 次） | | |
相关评论
