Oracle实现CLOB大小写不敏感检索遇到语法错误求助

问题修复方案 #

错误1：调用时的拼写与格式问题 #

你定义的CLOB转大写函数名为upper_clob，但报错查询中错写为upper_lob，且函数名与参数括号之间插入了不必要的换行，这是触发语法报错的直接原因。

错误2：upper_clob函数本身逻辑存在缺陷 #

原函数的写入偏移量计算逻辑错误，会导致转换后的CLOB内容错位、前半段为空：

• 原逻辑先将偏移量v_posn加4000再写入，第一块内容会直接写到第4001位，前4000位全为空
• 循环条件没有包含等于的边界，最后一段不足4000的内容会被遗漏

修复后的完整代码 #

1. 先修复upper_clob函数

CREATE OR REPLACE FUNCTION upper_clob(p_clob CLOB) RETURN CLOB
AS
    v_clob      CLOB;
    v_posn      NUMBER := 1;
    v_holder    VARCHAR2(4000);
    v_chunk_len NUMBER := 4000;
BEGIN
    IF p_clob IS NULL OR dbms_lob.getlength(p_clob) = 0 THEN
        RETURN p_clob;
    END IF;
    dbms_lob.createtemporary(v_clob, TRUE, dbms_lob.CALL);
    WHILE v_posn <= dbms_lob.getlength(p_clob) LOOP
        v_holder := dbms_lob.substr(p_clob, v_chunk_len, v_posn);
        v_holder := upper(v_holder);
        -- 改用追加写入避免偏移量计算错误
        dbms_lob.writeappend(v_clob, length(v_holder), v_holder);
        v_posn := v_posn + v_chunk_len;
    END LOOP;
    RETURN v_clob;
END;
/

2. 修改查询语句，修正函数名和格式

SELECT t.x id, l.s start_pos, l.e end_pos, dbms_lob.getlength(y) clob_len
FROM t OUTER APPLY search_clob(upper_clob(y), 'HELLO WORLD') l 
WHERE l.s > 0;

更优方案：无需转换CLOB，直接用原生大小写不敏感匹配 #

Oracle原生INSTR函数支持通过参数指定匹配规则，不需要提前转换整个CLOB，对大CLOB场景性能提升非常明显，直接修改search_clob函数即可实现大小写不敏感检索：

CREATE OR REPLACE FUNCTION search_clob_ci(p_lob clob, p_what varchar2)
RETURN se_tbl
PIPELINED
AS
    len int := length(p_what);
    s   int;
    pos int := 1;
BEGIN
    IF p_lob IS NOT NULL THEN
        LOOP
            -- 最后一个参数指定大小写不敏感匹配
            s := instr(p_lob, p_what, pos, 1, 'nls_sort=binary_ci');
            EXIT WHEN s = 0;
            PIPE ROW (se_obj(s, s + len - 1));
            pos := s + len;
        END LOOP;
    END IF;
    RETURN;
END;
/

调用时不需要转换CLOB，直接传原文和任意大小写的搜索词即可：

SELECT t.x id, l.s start_pos, l.e end_pos, dbms_lob.getlength(y) clob_len
FROM t OUTER APPLY search_clob_ci(y, 'Hello World') l 
WHERE l.s > 0;

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