ORA-22905错误求助：自定义文本替换函数无法访问非嵌套表项

错误原因 #

触发ORA-22905是因为在游标中访问TABLE(pPar)时，Oracle在解析阶段无法确定该集合的具体类型，即使参数pPar声明为custom.TBL_VARCHAR2，也需要显式通过CAST转换明确类型。

修复方案1：显式CAST转换 #

修改游标中的TABLE(pPar)部分，添加CAST强制转换以明确类型：

CREATE OR REPLACE FUNCTION custom.FN_REPLACETEXT(
    pText IN VARCHAR2, 
    pPar IN custom.TBL_VARCHAR2
) RETURN VARCHAR2
IS
    vText VARCHAR2(32767);
    vPos INT;
    vValue VARCHAR2(250);

    CURSOR cuParameter(POS INT) IS
    SELECT VAL
        FROM
            (
            SELECT VAL, ROWNUM AS RN 
            FROM (
                  SELECT COLUMN_VALUE VAL
                  -- 显式转换明确嵌套表类型
                  FROM TABLE(CAST(pPar AS custom.TBL_VARCHAR2))
                  )
            )
        WHERE RN=POS+1;
BEGIN
    vText := pText;
    FOR i IN 1..REGEXP_COUNT(pText, '[{][0-9]+[}]') LOOP
        vPos := TO_NUMBER(SUBSTR(REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i),2, LENGTH(REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i)) - 2));

        OPEN cuParameter(vPos);
        FETCH cuParameter INTO vValue;
        IF cuParameter%FOUND THEN
            vText := REPLACE(vText, REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i), vValue);
        END IF;
        CLOSE cuParameter;
    END LOOP;

    RETURN vText;

EXCEPTION
      WHEN OTHERS
      THEN
         RETURN pText;
END FN_REPLACETEXT;
/

优化方案：直接利用集合下标访问 #

嵌套表支持通过下标直接访问元素，无需游标查询，代码更简洁高效：

CREATE OR REPLACE FUNCTION custom.FN_REPLACETEXT(
    pText IN VARCHAR2, 
    pPar IN custom.TBL_VARCHAR2
) RETURN VARCHAR2
IS
    vText VARCHAR2(32767);
    vPos INT;
    vValue VARCHAR2(250);
BEGIN
    vText := pText;
    FOR i IN 1..REGEXP_COUNT(pText, '[{][0-9]+[}]') LOOP
        -- 提取占位符中的数字
        vPos := TO_NUMBER(SUBSTR(REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i),2, LENGTH(REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i)) - 2));
        
        -- 直接通过下标访问集合元素（Oracle集合下标从1开始，占位符从0开始，因此+1）
        IF vPos + 1 <= pPar.COUNT THEN
            vValue := pPar(vPos + 1);
            vText := REPLACE(vText, REGEXP_SUBSTR(pText, '[{][0-9]+[}]',1,i), vValue);
        END IF;
    END LOOP;

    RETURN vText;

EXCEPTION
      WHEN OTHERS
      THEN
         RETURN pText;
END FN_REPLACETEXT;
/

测试验证 #

执行测试语句：

select custom.FN_REPLACETEXT('Hello {0}, this is a {2} for {1}', custom.TBL_VARCHAR2('world','all','message')) as result from dual;

返回结果：Hello world, this is a message for all，符合预期。

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