建表
create table blob_table
(
id number(6) primary key,
name varchar2(10),
photo blob
)
建directory
create directory user_photo as 'G:照片'
插入数据
insert into blob_table values(1,'wangming',empty_blob())
insert into blob_table values(2,'guo',empty_blob())
自己写了个procedure 把二进制文件写到了表中，这就不贴出来了，总之photo这个栏位写入了一个文件，
现在的就是想把存到photo 这个栏位的内容写入二进制文件
过程如下：
CREATE OR REPLACE PROCEDURE SYS.download_file (NO NUMBER)
IS
blob_loc BLOB;
handle UTL_FILE.file_type;
photo_length INT;
amount INT;
offset INT
:= 1;
buffer RAW(1000);
temp INT;
v_name VARCHAR2 (10);
BEGIN
SELECT NAME, photo
INTO v_name, blob_loc
FROM blob_table
WHERE ID = NO;
photo_length := DBMS_LOB.getlength (blob_loc);
handle := UTL_FILE.fopen ('USER_PHOTO', v_name || '.jpg', 'w');
temp := TRUNC ((photo_length - 1) / 1000) + 1;
DBMS_OUTPUT.put_line (photo_length || '' || temp);
FOR i IN 1 .. temp LOOP
IF itemp THEN
amount := 1000;
ELSE
amount := photo_length - (temp - 1) * 1000;
END IF;
DBMS_OUTPUT.put_line (amount);
DBMS_LOB.READ (blob_loc, amount, offset, buffer);
offset := offset + 1000;
UTL_FILE.put_raw (handle, buffer);
END LOOP;
UTL_FILE.fclose (handle);
END download_file;
报错如下：
ORA-29285: 文件写入错误
ORA-06512: 在"SYS.UTL_FILE", line 18
ORA-06512: 在"SYS.UTL_FILE", line 1007
ORA-06512: 在"SYS.DOWNLOAD_FILE", line 32
ORA-06512: 在line 5
请求高手帮忙，谢谢!!
|
