APEX存储过程：CLOB转BLOB并解析数据至表遇问题求助

代码中的核心问题 #

• OUT参数l_output重复创建临时LOB，触发ORA-22275: 无效的LOB定位符指定错误
• 重复释放临时LOB对象，引发对象已释放的异常
• SOAP请求中的URL、报表路径硬编码，未使用传入的参数
• 仅查询解析后的CSV数据，未将结果插入目标表
• 异常处理逻辑不规范：未初始化v_name就执行插入操作，异常中盲目提交事务
• 注释语法错误（\--应改为标准的--）

优化后的存储过程代码 #

create or replace procedure "GET_SUPL"
(
    p_wsdl IN VARCHAR2,
    p_version IN VARCHAR2,
    p_username IN VARCHAR2,
    p_password IN VARCHAR2,
    p_wallet_path IN VARCHAR2 default null,
    p_wallet_pwd IN VARCHAR2 default null,
    p_rpt_path IN VARCHAR2,
    p_rpt_name IN VARCHAR2,
    r_out_id OUT NUMBER,
    l_output OUT BLOB
)
IS
    l_envelope   CLOB;
    l_result     XMLTYPE;
    l_base64     CLOB;
    v_out_id     NUMBER;
    v_sqlerrm    VARCHAR2 (4000);
begin
    -- 记录下载任务初始化状态
    insert into TG_OUTPUT_EXTERNAL_RPT (RPT_NAME, START_DATE_DOWNLOAD, STATUS_DOWNLOAD)
    values (p_rpt_name, sysdate, 'P')
    returning OUT_ID into v_out_id;
    r_out_id := v_out_id;

    -- 构建SOAP请求信封，使用传入的报表路径参数
    l_envelope := '
        <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
            <soap:Header/>
            <soap:Body>
                <pub:runReport>
                    <pub:reportRequest>
                        <pub:attributeFormat>csv</pub:attributeFormat>
                        <pub:flattenXML>false</pub:flattenXML>
                        <pub:parameterNameValues></pub:parameterNameValues>
                        <pub:reportAbsolutePath>' || p_rpt_path || '/' || p_rpt_name || '</pub:reportAbsolutePath>
                        <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
                    </pub:reportRequest>
                </pub:runReport>
            </soap:Body>
        </soap:Envelope>';

    -- 调用SOAP服务，使用传入的配置参数
    l_result := APEX_WEB_SERVICE.make_request (
        p_url                => p_wsdl,
        p_version            => p_version,
        p_envelope           => l_envelope,
        p_username           => p_username,
        p_password           => p_password,
        p_transfer_timeout   => 720,
        p_wallet_path        => p_wallet_path,
        p_wallet_pwd         => p_wallet_pwd
    );

    -- 解析返回的Base64编码报表数据
    l_base64 := APEX_WEB_SERVICE.parse_xml_clob (
        p_xml   => l_result,
        p_xpath => '//ns2:runReportResponse/ns2:runReportReturn/ns2:reportBytes/text()',
        p_ns    => 'xmlns:ns2="http://xmlns.oracle.com/oxp/service/PublicReportService"'
    );

    -- 将Base64转换为BLOB输出
    l_output := APEX_WEB_SERVICE.clobbase642blob (
        REPLACE(REPLACE(l_base64, CHR(10), NULL), CHR(13), NULL)
    );

    -- 将解析后的CSV数据插入目标表（需根据TEST_SUPL实际结构调整列名和跳过行数）
    INSERT INTO TEST_SUPL (col001)
    SELECT col001
    FROM TABLE(
        APEX_DATA_PARSER.parse(
            p_content      => l_output,
            p_file_name    => p_rpt_name || '.csv',
            p_skip_rows    => 1 -- 如果CSV含表头，跳过第一行
        )
    );

    -- 提交事务
    COMMIT;

    -- 释放本地创建的临时LOB资源
    DBMS_LOB.freetemporary(l_envelope);
    DBMS_LOB.freetemporary(l_base64);

EXCEPTION
    WHEN OTHERS THEN
        v_sqlerrm := SUBSTR(SQLERRM, 1, 4000);
        DBMS_OUTPUT.put_line('错误信息: ' || v_sqlerrm);
        
        -- 回滚事务，避免脏数据
        ROLLBACK;
        
        -- 更新任务状态为失败并记录错误
        UPDATE TG_OUTPUT_EXTERNAL_RPT 
        SET STATUS_DOWNLOAD = 'F', ERROR_MESSAGE = v_sqlerrm
        WHERE OUT_ID = v_out_id;
        COMMIT;
        
        -- 重新抛出异常，让调用方感知错误（可选）
        RAISE;
end;
/

关键修复说明 #

1. 修正LOB操作错误

   • 移除dbms_lob.createtemporary(l_output, true);，l_output作为OUT参数，由APEX_WEB_SERVICE.clobbase642blob直接生成有效BLOB对象赋值
   • 仅释放本地创建的l_envelope和l_base64临时LOB，且只执行一次释放操作

2. 参数化配置优化

   • SOAP请求URL使用传入的p_wsdl参数，报表路径通过p_rpt_path与p_rpt_name拼接，避免硬编码

3. 完善数据插入逻辑

   • 将原查询语句改为INSERT，把解析后的CSV数据写入TEST_SUPL表，需根据实际表结构调整列名和p_skip_rows参数

4. 异常处理规范

   • 异常发生时回滚事务，避免部分提交的脏数据
   • 更新TG_OUTPUT_EXTERNAL_RPT表的任务状态为失败，并记录错误信息
   • 可选重新抛出异常，让调用方明确感知错误

5. 语法与注释修正

   • 修正错误的注释符号\--为标准--
   • 恢复并完善下载任务状态记录逻辑

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