Oracle SQL Developer中向BLOB字段插入本地文件失败求助

问题场景 #

我正在学习SQL，已在Oracle SQL Developer中执行以下语句创建了application表：

CREATE TABLE application (
    applicationid      NUMBER,
    proofofidentity    BLOB NOT NULL,
    status             VARCHAR2(50) DEFAULT 'PENDING',
    userid       NUMBER NOT NULL,
    adminid     NUMBER NOT NULL
);

尝试向表中插入包含BLOB类型的数据时失败，执行的PL/SQL代码如下：

DECLARE
    l_bfile BFILE;
    l_blob  BLOB;
BEGIN

    l_bfile := BFILENAME('C:/Users/user name/Desktop/practice', 'proofofidentity_sample.pdf');
    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

    -- Initialize the BLOB
    DBMS_LOB.createtemporary(l_blob, TRUE);

    -- Load the file into the BLOB
    DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
    
    BEGIN 
        INSERT INTO application (applicationid, proofofidentity, status, userid, adminid)
        VALUES (1, l_blob, 'Approved', 1, 1);
    EXCEPTION
     WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Error in the INSERT: ' || SQLERRM);

END;
/

收到的错误信息：

Error report -
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 822
ORA-06512: at line 7
22285. 00000 -  "non-existent directory or file for %s operation"
*Cause:    Attempted to access a directory that does not exist, or attempted
           to access a file in a directory that does not exist.
*Action:   Ensure that a system object corresponding to the specified
           directory exists in the database dictionary, or
           make sure the name is correct.

问题根源 #

BFILENAME函数的第一个参数不是本地文件路径，而是Oracle数据库中已创建的DIRECTORY对象名称，直接传递本地路径会导致数据库无法识别，触发ORA-22285错误。

正确操作步骤 #

1. 创建数据库DIRECTORY对象 #

需要拥有DBA权限的用户（如SYS）创建指向本地文件目录的DIRECTORY对象：

-- 替换为你的实际本地目录路径，路径分隔符用/或转义的\
CREATE OR REPLACE DIRECTORY DOC_DIR AS 'C:/Users/user name/Desktop/practice';

2. 授予权限给当前用户 #

确保你使用的数据库用户拥有对该DIRECTORY的读写权限：

GRANT READ, WRITE ON DIRECTORY DOC_DIR TO YOUR_USER_NAME;

将YOUR_USER_NAME替换为你实际使用的数据库用户名。

3. 修改PL/SQL代码并执行 #

调整BFILENAME的参数为DIRECTORY对象名称，同时补充事务提交、资源释放逻辑：

DECLARE
    l_bfile BFILE;
    l_blob  BLOB;
BEGIN
    -- 使用DIRECTORY对象名称，而非本地路径
    l_bfile := BFILENAME('DOC_DIR', 'proofofidentity_sample.pdf');
    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

    -- 初始化临时BLOB
    DBMS_LOB.createtemporary(l_blob, TRUE);

    -- 将BFILE内容加载到BLOB
    DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
    
    BEGIN 
        INSERT INTO application (applicationid, proofofidentity, status, userid, adminid)
        VALUES (1, l_blob, 'Approved', 1, 1);
        COMMIT; -- 提交事务
    EXCEPTION
     WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('插入错误: ' || SQLERRM);
        ROLLBACK; -- 出错时回滚事务
    END;

    -- 关闭BFILE
    DBMS_LOB.fileclose(l_bfile);
    -- 释放临时BLOB资源
    DBMS_LOB.freetemporary(l_blob);
END;
/

额外注意事项 #

• 本地文件路径必须是数据库服务器可访问的路径：若Oracle为本地安装，直接使用本地路径即可；若为远程服务器，需将文件上传至服务器对应目录。
• 文件名拼写需准确：Linux/Unix系统下文件名大小写敏感，Windows系统下不敏感。
• 必须关闭BFILE并释放临时BLOB，避免数据库资源泄漏。

内容的提问来源于stack exchange，提问作者sks