操作系统文件转储成BLOB
CREATE OR REPLACE FUNCTION f_load_file(p_directory IN VARCHAR2,
p_filename IN VARCHAR2) RETURN BLOB IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
dbms_lob.createtemporary(dst_file, TRUE);
src_file := bfilename(p_directory, p_filename);
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.close(lob_loc => src_file);
RETURN dst_file;
END;
其中p_directory 是目录对象名称， p_filename是文件名
调用方式：
select f_load_file('IMGUPLOAD_DIR', '养生堂维C咀嚼片批准证书.JPG') from dual
如果遇到错误，ora-22290: operation would exceed the maximum number of opened files or lobs
采用如下方式解决： dbms_lob.filecloseall;
调试的时候，需要通过dbms_lob.createtemporary(dst_file, TRUE)将BLOB对象初始化， 否则会抛出错误：
ora-22275: invalid LOB locator specified
p_filename IN VARCHAR2) RETURN BLOB IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
dbms_lob.createtemporary(dst_file, TRUE);
src_file := bfilename(p_directory, p_filename);
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.close(lob_loc => src_file);
RETURN dst_file;
END;
其中p_directory 是目录对象名称， p_filename是文件名
调用方式：
select f_load_file('IMGUPLOAD_DIR', '养生堂维C咀嚼片批准证书.JPG') from dual
如果遇到错误，ora-22290: operation would exceed the maximum number of opened files or lobs
采用如下方式解决： dbms_lob.filecloseall;
调试的时候，需要通过dbms_lob.createtemporary(dst_file, TRUE)将BLOB对象初始化， 否则会抛出错误：
ora-22275: invalid LOB locator specified
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8520577/viewspace-1847885/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
