Oracle 21c ATP中ORDS返回大数据时触发ORA-06502错误

我在Oracle 21c ATP数据库中使用PL/SQL存储过程向ORDS处理器返回CLOB格式数据，当返回大量数据（如70行以上的采购订单表头及行项）时触发如下错误，少量数据时可正常运行：

错误信息 #

请求无法处理，因为评估与该资源关联的SQL语句时发生错误。请验证请求的URI和负载是否正确指定。若问题持续，请联系资源作者。SQL错误代码：6502，错误信息：ORA-06502: PL/SQL: value or conversion error ORA-06512: at line 9

PL/SQL存储过程 #

CREATE OR REPLACE PROCEDURE GET_PO_DETAILS(po_response OUT CLOB) AS
    l_po_header_json CLOB;
BEGIN
    -- 创建临时LOB
    DBMS_LOB.CREATETEMPORARY(po_response, TRUE);
    DBMS_LOB.CREATETEMPORARY(l_po_header_json, TRUE);

    -- 主查询：将表头和行项聚合为JSON数组
    SELECT JSON_ARRAYAGG( -- 外层JSON_ARRAYAGG返回完整CLOB
        JSON_OBJECT(
            'PO_HEADER_ID'    VALUE h.PO_HEADER_ID,
            'PO_NUMBER'       VALUE h.PO_NUMBER,
            'VENDOR_NAME'     VALUE h.VENDOR_NAME,
            'ORDER_DATE'      VALUE TO_CHAR(h.ORDER_DATE, 'YYYY-MM-DD'),
            'TOTAL_AMOUNT'    VALUE h.TOTAL_AMOUNT,
            'STATUS'          VALUE h.STATUS,
            'CREATED_BY'      VALUE h.CREATED_BY,
            'CREATED_DATE'    VALUE TO_CHAR(h.CREATED_DATE, 'YYYY-MM-DD'),
            'UPDATED_BY'      VALUE h.UPDATED_BY,
            'UPDATED_DATE'    VALUE TO_CHAR(h.UPDATED_DATE, 'YYYY-MM-DD'),
            'LINES' VALUE (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'PO_LINE_ID'       VALUE l.PO_LINE_ID,
                        'LINE_NUMBER'      VALUE l.LINE_NUMBER,
                        'ITEM_CODE'        VALUE l.ITEM_CODE,
                        'ITEM_DESCRIPTION' VALUE l.ITEM_DESCRIPTION,
                        'QUANTITY'         VALUE l.QUANTITY,
                        'UNIT_PRICE'       VALUE l.UNIT_PRICE,
                        'LINE_TOTAL'       VALUE l.LINE_TOTAL,
                        'CREATED_BY'       VALUE l.CREATED_BY,
                        'CREATED_DATE'     VALUE TO_CHAR(l.CREATED_DATE, 'YYYY-MM-DD'),
                        'UPDATED_BY'       VALUE l.UPDATED_BY,
                        'UPDATED_DATE'     VALUE TO_CHAR(l.UPDATED_DATE, 'YYYY-MM-DD')
                    )
                )
                FROM PO_LINES l
                WHERE l.PO_HEADER_ID = h.PO_HEADER_ID
            )
        )
    RETURNING CLOB
    )
    INTO l_po_header_json
    FROM PO_HEADER h;

    -- 赋值最终结果
    DBMS_LOB.APPEND(po_response, l_po_header_json);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_LOB.CREATETEMPORARY(po_response, TRUE);
        DBMS_LOB.WRITE(po_response, LENGTH(SQLERRM) + 100, 1,
            '{"status": "ERROR", "message": "Error while fetching PO details: ' || SQLERRM || '"}'
        );
END GET_PO_DETAILS;

ORDS GET模板处理器代码 #

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'ADMIN',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'admin',
      p_auto_rest_auth      => FALSE);    

  ORDS.DEFINE_MODULE(
      p_module_name    => 'bl',
      p_base_path      => '/bl/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'bl',
      p_pattern        => 'getData',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'bl',
      p_pattern        => 'getData',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare
    p_po_number VARCHAR2(100) := ''PO1005'';
    -- 存储JSON内容的CLOB变量
    v_clob CLOB;
    -- 用于解析JSON的JSON对象
    l_response JSON_OBJECT_T;
begin
    GET_PO_DETAILS(po_response => v_clob);
    --l_response := JSON_OBJECT_T.parse(v_clob);
    owa_util.mime_header (''application/json'', true); 
    htp.p(v_clob);
END;'
      );

  COMMIT; 
END;

数据库表结构 #

-- 创建PO_HEADER表
CREATE TABLE PO_HEADER (
    PO_HEADER_ID     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    PO_NUMBER        VARCHAR2(50) UNIQUE, 
    VENDOR_NAME      VARCHAR2(100),
    ORDER_DATE       DATE DEFAULT SYSDATE,
    TOTAL_AMOUNT     NUMBER(15,2) DEFAULT 0,
    STATUS           VARCHAR2(20) DEFAULT 'DRAFT',
    CREATED_BY       VARCHAR2(50),
    CREATED_DATE     DATE DEFAULT SYSDATE,
    UPDATED_BY       VARCHAR2(50),
    UPDATED_DATE     DATE
);

-- 创建无NOT NULL约束的PO_LINES表
CREATE TABLE PO_LINES (
    PO_LINE_ID       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    PO_HEADER_ID     NUMBER,  -- 无外键约束
    LINE_NUMBER      NUMBER,  -- 行号
    ITEM_CODE        VARCHAR2(50),
    ITEM_DESCRIPTION VARCHAR2(200),
    QUANTITY         NUMBER(10,2) DEFAULT 1 CHECK (QUANTITY > 0),
    UNIT_PRICE       NUMBER(10,2) DEFAULT 0 CHECK (UNIT_PRICE >= 0),
    LINE_TOTAL       NUMBER(15,2) GENERATED ALWAYS AS (QUANTITY * UNIT_PRICE) VIRTUAL, 
    CREATED_BY       VARCHAR2(50),
    CREATED_DATE     DATE DEFAULT SYSDATE,
    UPDATED_BY       VARCHAR2(50),
    UPDATED_DATE     DATE
);

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