blob/clob类型数据的存取方法

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;
BEGIN
-- 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;
--get file
lv_file := bfilename('FAC_INBOUND_CC', 'Blue hills.jpg');
--open file
dbms_lob.fileopen(lv_file, 0);
--get file length
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.fileclose(lv_file);
COMMIT;
--read photo
--get field
SELECT photo INTO lv_photo FROM fac.test_blob;
--read 2000 byte in every loop
lv_length := 2000;
-- create new file
-- utl_file.fremove('FAC_OUTBOUND', 'TEST_BLOB1.jpg');
lv_newfile := utl_file.fopen('FAC_OUTBOUND',
'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;
utl_file.fclose(lv_newfile);
END;