Oracle中将JSON数组转为CLOB作为PL/SQL参数的实现方法

你遇到的问题核心是json_array_t没有直接的to_clob()方法，同时原代码还存在CLOB未初始化、JSON字符串拼接风险的问题，以下是具体解决步骤：

关键修正点 #

• 必须先初始化CLOB变量，避免未初始化导致的赋值错误
• 使用json_array_t.write_to_clob()方法替代不存在的to_clob()
• 放弃字符串拼接JSON的方式，改用json_object_t.put()构建对象，避免单引号转义等语法错误

修正后的完整代码 #

Declare
    l_children json_array_t := json_array_t(); -- 初始化JSON数组
    p_clob CLOB;
    l_doc_id VARCHAR2(50);
    cursor getrec is select * from sometable;
begin
    -- 初始化临时CLOB
    dbms_lob.createtemporary(p_clob, true);
    
    for i in getrec loop
        -- 生成文档ID
        l_doc_id := TO_CHAR(SYSTIMESTAMP,'mmddyyyyhhmiss');
        
        -- 构建第一个JSON对象
        declare
            l_obj1 json_object_t := json_object_t();
        begin
            l_obj1.put('AttachedDocumentId', l_doc_id);
            l_obj1.put('DatatypeCode', 'TEXT');
            l_obj1.put('CategoryName', 'INSTANCE_NUMBER');
            l_obj1.put('UploadedText', i.instance_number);
            l_obj1.put('UploadedFileContentType', 'text');
            l_obj1.put('ContentRepositoryFileShared', 'false');
            l_obj1.put('Title', i.instance_number);
            l_obj1.put('Description', i.instance_number);
            l_children.append(l_obj1);
        end;
        
        -- 生成第二个文档ID
        l_doc_id := TO_CHAR(SYSTIMESTAMP,'mmddyyyyhhmiss');
        
        -- 构建第二个JSON对象
        declare
            l_obj2 json_object_t := json_object_t();
        begin
            l_obj2.put('AttachedDocumentId', l_doc_id);
            l_obj2.put('DatatypeCode', 'TEXT');
            l_obj2.put('CategoryName', 'SERIAL_NUMBER');
            l_obj2.put('UploadedText', i.attachment_serial_number);
            l_obj2.put('UploadedFileContentType', 'text');
            l_obj2.put('ContentRepositoryFileShared', 'false');
            l_obj2.put('Title', i.attachment_serial_number);
            l_obj2.put('Description', i.attachment_serial_number);
            l_children.append(l_obj2);
        end;
    end loop;
    
    -- 将JSON数组写入CLOB
    l_children.write_to_clob(p_clob);
    
    -- 这里可以使用p_clob作为参数传递
    -- 示例：some_procedure(p_clob);
    
    -- 释放临时CLOB
    dbms_lob.freetemporary(p_clob);
exception
    when others then
        -- 异常处理时也要释放CLOB
        if dbms_lob.istemporary(p_clob) = 1 then
            dbms_lob.freetemporary(p_clob);
        end if;
        raise;
end;
/

额外说明 #

• 使用dbms_lob.createtemporary创建临时CLOB，用完后必须用freetemporary释放，避免内存泄漏
• 用json_object_t.put()构建JSON对象，能自动处理特殊字符（比如单引号）的转义，避免JSON语法错误
• write_to_clob()是Oracle JSON API中用于将JSON数组写入CLOB的标准方法，替代你原本错误调用的to_clob()

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