如何解决传递大量JSON时的'unimplemented or unreasonable conversion requested'错误

针对你遇到的大量JSON记录传递报错问题，以下是几种可行的解决方法：

方案1：分段批量传递记录 #

将大量记录拆分成多个小批次，客户端分批调用服务器函数，避免单次传递的字符串/CLOB长度超出限制。

客户端代码示例： #

DECLARE
    SelectionVar VARCHAR2(32000);
    invoiceExist VARCHAR2(10) := 'N';
    total_records NUMBER := 10000; -- 总记录数
    batch_size NUMBER := 1000; -- 每批次记录数
    start_idx NUMBER;
    end_idx NUMBER;
BEGIN
    FOR batch IN 1 .. CEIL(total_records / batch_size) LOOP
        start_idx := (batch - 1) * batch_size + 1;
        end_idx := LEAST(batch * batch_size, total_records);
        -- 生成当前批次的JSON字符串
        stringify(rec(start_idx..end_idx), "IS_TRANSACTION=${IsTransaction}^COMPANY_ID=${CompanyId}^PR_TRANSACTION_SEQ=${PrTransactionSeq}^SPRICE=${SPrice}^SQUANTITY=${SQuantity}^", JSON) into SelectionVar;
        -- 调用服务器函数，只要某批次存在发票就标记为存在
        IF Get_Records(SelectionVar) = 'Y' THEN
            invoiceExist := 'Y';
        END IF;
    END LOOP;
END;
/

方案2：使用Oracle集合类型替代JSON传递 #

放弃JSON序列化，直接使用Oracle自定义的记录集合类型传递数据，避免JSON转换的开销和长度限制，同时提升处理效率。

步骤1：服务器端定义自定义类型 #

-- 定义单条记录的类型
CREATE OR REPLACE TYPE RECORD_TYPE AS OBJECT (
    IS_TRANSACTION VARCHAR2(10),
    COMPANY_ID NUMBER,
    PR_TRANSACTION_SEQ NUMBER,
    SPRICE NUMBER,
    SQUANTITY NUMBER
);
/
-- 定义记录数组类型
CREATE OR REPLACE TYPE RECORD_ARRAY AS TABLE OF RECORD_TYPE;
/

步骤2：修改服务器端Get_Records函数 #

FUNCTION Get_Records(
   records_  IN RECORD_ARRAY )  RETURN VARCHAR2
IS 
   invoiceExist VARCHAR2(10) := 'N';
BEGIN
   -- 直接遍历集合处理每条记录
   FOR i IN records_.FIRST .. records_.LAST LOOP
       -- 执行你的修改逻辑，例如更新表或检查发票存在性
       IF EXISTS (SELECT 1 FROM invoices WHERE COMPANY_ID = records_(i).COMPANY_ID) THEN
           invoiceExist := 'Y';
       END IF;
   END LOOP;
   RETURN invoiceExist;
END;
/

步骤3：客户端调用代码 #

DECLARE
    rec_array RECORD_ARRAY := RECORD_ARRAY();
    invoiceExist VARCHAR2(10);
BEGIN
    -- 批量添加记录到集合
    rec_array.EXTEND(1000); -- 扩展集合容量
    rec_array(1) := RECORD_TYPE('Y', 123, 456, 100.0, 5);
    -- 继续添加更多记录...
    rec_array(1000) := RECORD_TYPE('N', 789, 1011, 200.0, 2);
    
    -- 调用服务器函数
    invoiceExist := Get_Records(rec_array);
END;
/

方案3：优化CLOB类型的JSON传递 #

如果必须使用JSON，确保客户端直接生成并传递CLOB类型，而非先转成VARCHAR2再转CLOB，同时优化服务器端的JSON解析逻辑。

服务器端修改后的函数 #

FUNCTION Get_Records(
   selection_clob_  IN CLOB )  RETURN VARCHAR2
IS 
   records_               json_array := json_array.parse(selection_clob_);
   invoiceExist VARCHAR2(10) := 'N';
   current_obj            json_object;
BEGIN
    -- 遍历JSON数组处理每条记录
    FOR i IN 0 .. records_.get_size() - 1 LOOP
        current_obj := records_.get(i).to_json_object();
        -- 执行你的修改或检查逻辑
        IF current_obj.get_number('COMPANY_ID') IS NOT NULL THEN
            -- 示例逻辑：检查发票是否存在
            IF EXISTS (SELECT 1 FROM invoices WHERE COMPANY_ID = current_obj.get_number('COMPANY_ID')) THEN
                invoiceExist := 'Y';
            END IF;
        END IF;
    END LOOP;
    RETURN invoiceExist;
END;
/

客户端生成CLOB的代码示例 #

DECLARE
    SelectionVar CLOB;
    invoiceExist VARCHAR2(10);
BEGIN
    -- 直接从数据源生成CLOB类型的JSON数组，避免中间VARCHAR2转换
    SELECT json_arrayagg(
        json_object(
            'IS_TRANSACTION' VALUE IsTransaction,
            'COMPANY_ID' VALUE CompanyId,
            'PR_TRANSACTION_SEQ' VALUE PrTransactionSeq,
            'SPRICE' VALUE SPrice,
            'SQUANTITY' VALUE SQuantity
        )
    ) INTO SelectionVar FROM your_source_table; -- 替换为你的记录来源
    
    invoiceExist := Get_Records(SelectionVar);
END;
/

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