如何传递50k字符至Oracle函数参数避免ORA-01704错误？

问题描述 #

向Oracle函数传递超过40k字符的输入参数时，触发ORA-01704: string literal too long错误。业务需求需传递约50k字符作为输入参数，尝试将参数类型替换为CLOB、使用自定义对象类型CHECK_TYP后仍报错，请求解决该问题。原函数代码如下：

create or replace function f_abc(
    ids in varchar2  --tried CLOB too
    --INID_CHECK IN CHECK_TYP   --Tried object type too
    )
  return varchar2
is
  return_value varchar2(32000);
  countt number(2);
begin

    for i in (select trim(regexp_substr(ids, '[^,]+', 1, level)) prm from dual connect by level <= regexp_count(ids, ',')+1)
    --FOR i IN 1 .. INID_CHECK.count
    loop
        countt:=0;
        begin
        select count(*)
        into countt
        from x.names
        where name = i.prm  
        and ind='Y';
        if countt = 0 then
            begin
            select count(*)
            into countt
            from x.rt_names
            where name = i.prm  
            and ind='Y';
            if countt = 0 then
                return_value := return_value||','||i.prm 
                ;
            end if;
            end;
        end if;
        end;
    end loop;
if return_value = '' then
    return '';
else
    return return_value;
end if;
end;

解决方案 #

1. 适配CLOB参数的拆分逻辑

原代码用regexp_substr+connect by处理CLOB时，会因为CLOB的长度限制和递归效率问题报错，改用DBMS_LOB的API来拆分大CLOB：

create or replace function f_abc(
    ids in CLOB
) return varchar2
is
    return_value varchar2(32000);
    countt number(2);
    v_start number := 1;
    v_end number;
    v_delimiter char(1) := ',';
    v_prm varchar2(4000);
begin
    -- 循环拆分CLOB中的逗号分隔值
    loop
        v_end := DBMS_LOB.INSTR(ids, v_delimiter, v_start);
        if v_end = 0 then
            v_prm := TRIM(DBMS_LOB.SUBSTR(ids, DBMS_LOB.GETLENGTH(ids) - v_start + 1, v_start));
            exit;
        else
            v_prm := TRIM(DBMS_LOB.SUBSTR(ids, v_end - v_start, v_start));
            v_start := v_end + 1;
        end if;

        -- 检查x.names表
        select count(*) into countt
        from x.names
        where name = v_prm and ind='Y';
        
        if countt = 0 then
            -- 检查x.rt_names表
            select count(*) into countt
            from x.rt_names
            where name = v_prm and ind='Y';
            
            if countt = 0 then
                -- 拼接结果，避免空值时多余逗号
                return_value := case 
                    when return_value is null then v_prm 
                    else return_value || ',' || v_prm 
                end;
            end if;
        end if;
    end loop;

    return nvl(return_value, '');
end;
/

2. 正确调用函数，避免直接传入超长字面量

即使函数参数是CLOB，直接写f_abc('50k字符的字符串')仍会触发错误——因为字符串字面量本身超过了Oracle对VARCHAR2字面量的长度限制（12c+为32767字节，之前版本为4000字节）。需要先将内容存入CLOB变量再传递：

declare
    v_clob CLOB;
    v_result varchar2(32000);
begin
    -- 初始化CLOB（如果内容来自外部，可通过DBMS_LOB写入）
    v_clob := '这里是约50k字符的超长逗号分隔内容...';
    v_result := f_abc(v_clob);
    dbms_output.put_line(v_result);
end;
/

3. 性能优化（可选）

如果拆分的参数数量较多，循环单条查询会导致性能低下，建议改用批量查询方式：

-- 先定义嵌套表类型
create or replace type str_tab is table of varchar2(4000);
/

create or replace function f_abc(
    ids in CLOB
) return varchar2
is
    return_value varchar2(32000);
    v_tab str_tab := str_tab();
    v_start number := 1;
    v_end number;
    v_delimiter char(1) := ',';
    v_prm varchar2(4000);
begin
    -- 拆分CLOB到集合
    loop
        v_end := DBMS_LOB.INSTR(ids, v_delimiter, v_start);
        if v_end = 0 then
            v_prm := TRIM(DBMS_LOB.SUBSTR(ids, DBMS_LOB.GETLENGTH(ids) - v_start + 1, v_start));
            v_tab.extend;
            v_tab(v_tab.count) := v_prm;
            exit;
        else
            v_prm := TRIM(DBMS_LOB.SUBSTR(ids, v_end - v_start, v_start));
            v_tab.extend;
            v_tab(v_tab.count) := v_prm;
            v_start := v_end + 1;
        end if;
    end loop;

    -- 批量查询不在两张表中的参数，用listagg拼接结果
    select listagg(column_value, ',') within group (order by column_value)
    into return_value
    from table(v_tab) t
    where not exists (
        select 1 from x.names n where n.name = t.column_value and n.ind='Y'
    )
    and not exists (
        select 1 from x.rt_names rn where rn.name = t.column_value and rn.ind='Y'
    );

    return nvl(return_value, '');
end;
/

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