Oracle中读写文件

Create Table Blob_EXAMPLE (
ID number(6) primary key,
Name varchar2(20),
Resume Blob)

insert into blob_example values(1,'LONG1',empty_blob());
insert into blob_example values(2,'LONG2',empty_blob());

CREATE OR REPLACE DIRECTORY DOCS AS 'G:\Oracle\File'

grant write,read on directory docs to long1

CREATE OR REPLACE PROCEDURE update_blob_doc
(t_id number, filename varchar2 ) as
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file:=BFILENAME('DOCS',filename);
Select Resume INTO dst_file FROM blob_example
Where id=t_id FOR UPDATE;
DBMS_LOB.fileopen(src_file,dbms_lob.file_readonly);
lgh_file:=dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file,src_file,lgh_file);
update blob_example set resume=dst_file
where id=t_id;
dbms_lob.fileclose(src_file);
commit;
end;

CREATE OR REPLACE PROCEDURE Read_Blob_doc(
t_id number,filename varchar2) as
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER:=32767;
l_pos NUMBER:=1;
l_Blob Blob;
l_Blob_len Number;
BEGIN
SELECT resume Into l_Blob FROM Blob_example
WHERE id=t_id;
l_Blob_len:=dbms_lob.getlength(l_Blob);
l_file:=UTL_FILE.FOPEN('DOCS',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
IF UTL_FILE.IS_OPEN(l_file) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
RAISE;
END;