如何在SQL*Plus中调用带BLOB输入参数的存储过程？

核心思路 #

由于SQL*Plus对直接传递大字节数据存在限制，需通过临时LOB对象承载超过4000字节的BLOB数据，再将其作为参数传入目标存储过程。

完整调用脚本（适配大BLOB） #

-- 1. 声明绑定变量，用于接收存储过程输出的ID
VAR v_output_id NUMBER;

-- 2. 执行PL/SQL块完成BLOB加载与存储过程调用
DECLARE
    v_session_id VARCHAR2(100) := 'SESSION_20240520'; -- 替换为实际sessionId
    v_account_id INTEGER := 12345; -- 替换为实际accountId
    v_lob_data BLOB;
    v_src_file BFILE;
    v_file_size INTEGER;
BEGIN
    -- 创建临时LOB对象
    DBMS_LOB.CREATETEMPORARY(v_lob_data, TRUE);
    
    -- 关联本地文件（需先在数据库中创建对应DIRECTORY对象）
    -- 假设已创建名为LOB_FILE_DIR的目录，指向服务器上的文件路径（如'/opt/oracle/upload/'）
    v_src_file := BFILENAME('LOB_FILE_DIR', 'large_sample.pdf'); -- 替换为实际文件名
    
    -- 打开文件准备读取
    DBMS_LOB.OPEN(v_src_file, DBMS_LOB.LOB_READONLY);
    
    -- 获取文件总大小
    v_file_size := DBMS_LOB.GETLENGTH(v_src_file);
    
    -- 将文件内容加载到临时LOB中
    DBMS_LOB.LOADFROMFILE(
        dest_lob => v_lob_data,
        src_lob => v_src_file,
        amount => v_file_size
    );
    
    -- 关闭源文件
    DBMS_LOB.CLOSE(v_src_file);
    
    -- 调用目标存储过程
    p_dummy_proc(
        sessionId => v_session_id,
        accountId => v_account_id,
        lobData => v_lob_data,
        oId => :v_output_id
    );
    
    -- 释放临时LOB资源
    DBMS_LOB.FREETEMPORARY(v_lob_data);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常分支：确保资源被正确释放
        IF DBMS_LOB.ISOPEN(v_src_file) = 1 THEN
            DBMS_LOB.CLOSE(v_src_file);
        END IF;
        IF DBMS_LOB.ISTEMPORARY(v_lob_data) = 1 THEN
            DBMS_LOB.FREETEMPORARY(v_lob_data);
        END IF;
        RAISE; -- 抛出异常便于排查问题
END;
/

-- 查看存储过程返回的输出ID
PRINT v_output_id;

关键注意事项 #

• DIRECTORY对象配置：需由DBA预先创建数据库目录并授权，示例命令：

  CREATE DIRECTORY LOB_FILE_DIR AS '/opt/oracle/upload/';
  GRANT READ ON DIRECTORY LOB_FILE_DIR TO YOUR_DB_USER;
  

• 服务器文件权限：Oracle数据库所在服务器需能访问指定的文件路径，确保操作系统层面权限足够。
• 临时LOB管理：必须在使用完毕后释放临时LOB，避免占用数据库临时资源。
• 异常处理：脚本中添加了资源清理的异常分支，防止因报错导致文件或LOB资源泄漏。

小BLOB数据的简化调用（≤4000字节） #

如果BLOB数据长度不超过4000字节，可直接通过字符串转换传入，无需临时LOB：

VAR v_output_id NUMBER;
DECLARE
    v_session_id VARCHAR2(100) := 'SESSION_20240520';
    v_account_id INTEGER := 12345;
    v_small_lob BLOB;
BEGIN
    -- 将字符串转换为BLOB
    v_small_lob := UTL_RAW.CAST_TO_RAW('小型测试数据，长度不超过4000字节');
    -- 调用存储过程
    p_dummy_proc(v_session_id, v_account_id, v_small_lob, :v_output_id);
    COMMIT;
END;
/
PRINT v_output_id;

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