--blob 的讀寫
CREATE OR REPLACE PROCEDURE P_IMG_INSERT (v_filename VARCHAR2)
IS
v_bfile BFILE;--文件指針
v_blob BLOB;
DIR CONSTANT VARCHAR2(20) := 'TEST';--文件存放DIRECTORY,區分大小寫
V_DEST NUMBER := 1;
V_LANG NUMBER := 1;
BEGIN
/**//*通過empty_blob()函數將類型為blob的列初始化為空以便以后填充*/
INSERT INTO res_info (res_blob)
VALUES (EMPTY_BLOB ()) RETURN res_blob INTO v_blob;
v_bfile:= BFILENAME (DIR, v_filename);
IF (dbms_lob.fileexists(v_bfile)!=0) THEN
dbms_lob.fileopen(v_bfile,dbms_lob.file_readonly); --打開目標文件
/**//*將文件字數據加載到指定的LOB類型變量*/
dbms_lob.loadblobfromfile(v_blob,
v_bfile,
dbms_lob.getlength(v_bfile),
V_DEST,
V_LANG);
-- dbms_lob.loadblobfromfile
dbms_lob.fileclose(v_bfile);--關閉文件
COMMIT;
dbms_output.put_line('已經從'||DIR||'目錄中讀取了文件'||v_filename||'向表中插入');
ELSE--如果文件定位器指向的文件不存在
dbms_output.put_line('文件沒找到');
END IF;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;說明下:
DBMS_LOB.LOADBLOBFROMFILE (
dest_lob IN OUT NOCOPY BLOB,
src_bfile IN BFILE,
amount IN INTEGER,
dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER);
| Parameter |
Description |
| dest_lob |
BLOB locator of the target for the load. |
| src_bfile |
BFILE locator of the source for the load. |
| amount |
Number of bytes to load from the BFILE. You can also use DBMS_LOB.LOBMAXSIZE to load until the end of the BFILE. |
| dest_offset |
(IN) Offset in bytbes in the destination BLOB (origin: 1) for the start of the write. (OUT) New offset in bytes in the destination BLOB right after the end of this write, which is also where the next write should begin. |
| src_offset |
(IN) Offset in bytes in the source BFILE (origin: 1) for the start of the read .(OUT) Offset in bytes in the source BFILE right after the end of this read, which is also where the next read should begin. |