CREATE OR REPLACE PROCEDURE TEST_BLOB IS
lv_photo BLOB;
lv_file BFILE;
lv_length INT;
src_offset INT := 1;
dest_offset INT := 1;
lv_buffer RAW(2000);
lv_newfile utl_file.file_type;
lv_file BFILE;
lv_length INT;
src_offset INT := 1;
dest_offset INT := 1;
lv_buffer RAW(2000);
lv_newfile utl_file.file_type;
BEGIN
-- write photo
DELETE FROM fac.test_blob;
COMMIT;
INSERT INTO fac.test_blob VALUES ('AAA', empty_blob());
-- write photo
DELETE FROM fac.test_blob;
COMMIT;
INSERT INTO fac.test_blob VALUES ('AAA', empty_blob());
COMMIT;
--lock record
SELECT photo INTO lv_photo FROM fac.test_blob FOR UPDATE;
SELECT photo INTO lv_photo FROM fac.test_blob FOR UPDATE;
--get file
lv_file := bfilename('FAC_INBOUND_CC', 'Blue hills.jpg');
lv_file := bfilename('FAC_INBOUND_CC', 'Blue hills.jpg');
--open file
dbms_lob.fileopen(lv_file, 0);
dbms_lob.fileopen(lv_file, 0);
--get file length
lv_length := dbms_lob.getlength(lv_file);
lv_length := dbms_lob.getlength(lv_file);
--write file to table
dbms_lob.loadblobfromfile(lv_photo,
lv_file,
lv_length,
dest_offset,
src_offset);
dbms_lob.loadblobfromfile(lv_photo,
lv_file,
lv_length,
dest_offset,
src_offset);
dbms_lob.fileclose(lv_file);
COMMIT;
--read photo
--get field
SELECT photo INTO lv_photo FROM fac.test_blob;
SELECT photo INTO lv_photo FROM fac.test_blob;
--read 2000 byte in every loop
lv_length := 2000;
lv_length := 2000;
-- create new file
-- utl_file.fremove('FAC_OUTBOUND', 'TEST_BLOB1.jpg');
-- utl_file.fremove('FAC_OUTBOUND', 'TEST_BLOB1.jpg');
lv_newfile := utl_file.fopen('FAC_OUTBOUND',
'TEST_BLOB' ||
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || '.jpg',
'W',
32767);
'TEST_BLOB' ||
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || '.jpg',
'W',
32767);
LOOP
BEGIN
-- read data
dbms_lob.READ(lv_photo, lv_length, src_offset, lv_buffer);
--write to file
utl_file.put_raw(lv_newfile, lv_buffer);
--start position
src_offset := src_offset + 2000;
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
END LOOP;
BEGIN
-- read data
dbms_lob.READ(lv_photo, lv_length, src_offset, lv_buffer);
--write to file
utl_file.put_raw(lv_newfile, lv_buffer);
--start position
src_offset := src_offset + 2000;
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
END LOOP;
utl_file.fclose(lv_newfile);
END;
因为每次读取只能2000 byte, 因此需要用循环来取。然后用exception来判断是否到文件尾.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10359218/viewspace-677428/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10359218/viewspace-677428/