Oracle函数定义CLOB输入参数遇ORA-01704错误求助

这个问题我之前帮很多人解决过，核心原因是你直接把CLOB类型的参数丢进IN子句里了——Oracle的IN子句没办法直接处理CLOB对象，而且就算你尝试把CLOB转成普通字符串，也会因为长度超过varchar2的上限（通常是4000字符，12c+扩展到32767）触发ORA-01704错误。下面给你两个靠谱的解决方案：

方案一：用XMLTABLE拆分CLOB（Oracle 12c+推荐） #

XMLTABLE的tokenize函数可以直接按分隔符拆分CLOB为多行，不需要额外创建自定义函数，代码更简洁：

CREATE OR REPLACE FUNCTION func_name (START_DATE NUMBER, END_DATE NUMBER, NAME CLOB)
RETURN SYS_REFCURSOR
IS
  v_result SYS_REFCURSOR;
BEGIN
  OPEN v_result FOR
    SELECT t.*
    FROM table_name t
    -- 按逗号拆分CLOB为多行的name_val
    JOIN XMLTABLE(
      'tokenize($str, ",")'
      PASSING NAME AS "str"
      COLUMNS name_val VARCHAR2(100) PATH '.' -- 根据你的name_desc字段长度调整
    ) x ON TRIM(t.name_desc) = TRIM(x.name_val) -- 加TRIM避免空格导致匹配失败
    WHERE t.start_date_col = START_DATE -- 替换成你实际的日期列名
      AND t.end_date_col = END_DATE; -- 替换成你实际的日期列名
  RETURN v_result;
END;
/

方案二：自定义表函数拆分CLOB（兼容Oracle 11g及更早版本） #

如果你的Oracle版本不支持tokenize，可以先创建一个拆分CLOB的表函数，再在主函数中调用：

第一步：创建自定义类型和拆分函数 #

-- 定义存储拆分结果的表类型
CREATE OR REPLACE TYPE t_name_list AS TABLE OF VARCHAR2(100);
/

-- 拆分CLOB为表的函数
CREATE OR REPLACE FUNCTION split_clob_to_list(p_clob CLOB, p_delimiter VARCHAR2 := ',')
RETURN t_name_list
IS
  v_start NUMBER := 1;
  v_end NUMBER;
  v_len NUMBER := DBMS_LOB.GETLENGTH(p_clob);
  v_list t_name_list := t_name_list();
BEGIN
  -- 处理空CLOB的情况
  IF v_len IS NULL OR v_len = 0 THEN
    RETURN v_list;
  END IF;
  
  LOOP
    -- 找到下一个分隔符的位置
    v_end := DBMS_LOB.INSTR(p_clob, p_delimiter, v_start);
    -- 如果是最后一段，把结束位置设为CLOB长度+1
    IF v_end = 0 THEN
      v_end := v_len + 1;
    END IF;
    -- 截取子串并加入结果列表
    v_list.EXTEND;
    v_list(v_list.COUNT) := TRIM(DBMS_LOB.SUBSTR(p_clob, v_end - v_start, v_start));
    -- 移动起始位置到下一段
    v_start := v_end + 1;
    EXIT WHEN v_start > v_len;
  END LOOP;
  
  RETURN v_list;
END;
/

第二步：修改你的主函数 #

CREATE OR REPLACE FUNCTION func_name (START_DATE NUMBER, END_DATE NUMBER, NAME CLOB)
RETURN SYS_REFCURSOR
IS
  v_result SYS_REFCURSOR;
BEGIN
  OPEN v_result FOR
    SELECT t.*
    FROM table_name t
    -- 用TABLE函数把CLOB拆分后的列表作为IN子句的数据源
    WHERE TRIM(t.name_desc) IN (SELECT TRIM(column_value) FROM TABLE(split_clob_to_list(NAME)))
      AND t.start_date_col = START_DATE
      AND t.end_date_col = END_DATE;
  RETURN v_result;
END;
/

注意事项 #

• 请根据你实际的name_desc字段长度，调整代码中VARCHAR2(100)的数值，确保能容纳单个名称的长度。
• 如果你的CLOB参数中包含带空格的名称（比如"Alice, Bob, Charlie"），一定要保留TRIM函数，避免因为空格导致匹配失败。
• 如果你不需要返回游标，也可以把函数的返回类型改成你需要的表类型或其他类型，调整对应的逻辑即可。

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