Oracle ORDS PL/SQL存储过程CLOB输出超32767字符报错求助

问题背景 #

通过Python/Postman调用Oracle ORDS定义的PL/SQL存储过程获取o_json_clob值时，当该CLOB字符数≤32767可正常返回，但超过该长度时存储过程报错。但在Oracle SQL Developer中调用同一存储过程却能正常运行，需要解决超长CLOB的获取问题。

解决方案 #

1. 将ORDS处理程序改为返回CLOB的函数形式 #

ORDS对PL/SQL块的输出默认存在VARCHAR2长度限制（32767字符），改用函数返回CLOB可绕过此限制：

-- 1. 创建封装存储过程的函数
CREATE OR REPLACE FUNCTION apps.XXOM_PRJWS_GET_PRICE_CONFIG_FUNC(p_body_text CLOB) RETURN CLOB IS
    o_json_clob CLOB;
BEGIN
    apps.XXOM_PRJWS_API_PKG.get_price_config_details(i_json_clob => p_body_text, o_json_clob => o_json_clob);
    RETURN o_json_clob;
END;
/

-- 2. 重新定义ORDS处理程序
ORDS.DEFINE_HANDLER(
    p_module_name    => 'EMRPRJWSTV1.0',
    p_pattern        => 'getPriceConfigDetails',
    p_method         => 'GET',
    p_source_type    => 'plsql/function',
    p_mimes_allowed  => 'application/json',
    p_comments       => NULL,
    p_source         => 'BEGIN RETURN apps.XXOM_PRJWS_GET_PRICE_CONFIG_FUNC(:body_text); END;'
);

2. 在PL/SQL块中直接输出CLOB内容 #

通过htp.p直接输出CLOB，让ORDS将输出作为响应返回，无需依赖变量传递限制：

ORDS.DEFINE_HANDLER(
    p_module_name    => 'EMRPRJWSTV1.0',
    p_pattern        => 'getPriceConfigDetails',
    p_method         => 'GET',
    p_source_type    => 'plsql/block',
    p_mimes_allowed  => 'application/json',
    p_comments       => NULL,
    p_source         => '
DECLARE
    o_json_clob                CLOB;
    v_appname                  VARCHAR2(1000);
BEGIN
    apps.XXOM_PRJWS_API_PKG.get_price_config_details(i_json_clob => :body_text, o_json_clob => o_json_clob);
    htp.p(o_json_clob); -- 直接输出CLOB内容
END;'
);

3. 确保存储过程中CLOB的正确初始化 #

存储过程中未正确初始化CLOB可能导致超长拼接时出错，显式初始化临时LOB：

CREATE OR REPLACE PACKAGE BODY APPS.xxom_prjws_api_pkg IS
    PROCEDURE get_price_config_details(i_json_clob       CLOB,
                                      o_json_clob   OUT CLOB) IS
    BEGIN
        -- 初始化临时CLOB
        DBMS_LOB.CREATETEMPORARY(o_json_clob, TRUE);
        DBMS_LOB.OPEN(o_json_clob, DBMS_LOB.LOB_READWRITE);
        
        -- 原有拼接逻辑
        DBMS_LOB.APPEND(o_json_clob, TO_CLOB('"item_type_code":"'|| l_line_rec_tbl(j).item_type_code||'",'));
        DBMS_LOB.APPEND(o_json_clob, TO_CLOB('"line_number":"'|| l_line_rec_tbl(j).line_number||'",'));
        
        -- 关闭临时LOB
        DBMS_LOB.CLOSE(o_json_clob);
    END;
END;
/

4. 调整ORDS的最大响应长度参数 #

修改ORDS配置，允许更大的响应长度：

BEGIN
    ORDS.SET_PARAMETER('plsql.max_response_length', '2000000'); -- 根据业务需求设置合适值（示例为2MB）
END;
/

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