PL/SQL函数报错ORA-00932:数据类型不一致，求排查解决

问题背景 #

执行自定义PL/SQL函数时触发ORA-00932: inconsistent datatypes: expected - got -错误，相关代码如下：

表定义 #

CREATE TABLE TEST_TABLE (ID NUMBER, COLUMN1 VARCHAR2(200));
Insert into TEST_TABLE(ID, COLUMN1) Values(1,'200:300');
Insert into TEST_TABLE(ID, COLUMN1) Values(2,'5');

自定义类型 #

create or replace TYPE "DUMMY_RECORD_TEST" as object (
    ID                           NUMBER,
    COLUMN_LIST                  varchar2(100));

create or replace TYPE "DUMMY_TABLE_TEST" as table of DUMMY_RECORD_TEST

函数代码 #

FUNCTION DUMMYFUNCTION(
    p_id_name             in VARCHAR2,
    p_column_name         in VARCHAR2,
    p_tablename           in VARCHAR2,
    p_type                in VARCHAR2,
    p_delimter            in VARCHAR2
)
return DUMMY_TABLE_TEST

as 

sql_qry     VARCHAR2(4000);
csr         SYS_REFCURSOR;  
dummy_records DUMMY_TABLE_TEST;

BEGIN   

    sql_qry := 'Select DUMMY_RECORD_TEST(' || p_id_name || ',
      trim(regexp_substr(' || p_column_name || ',''[^:]+'', 1, lines.column_value)))
        From ' || p_tablename || ',
             Table (CAST (Multiset( Select 
                                        Level 
                                    From dual 
                                    Connect by instr(' || p_column_name || ','':'', 1, Level - 1) > 0
                        ) as sys.ODCIVARCHAR2LIST 
                    )
                ) lines fetch first 1 rows only';

    Open csr For sql_qry;
    Loop
        Fetch csr Bulk collect into dummy_records Limit 3;
            Exit When csr%NOTFOUND;
        End Loop;
        Close csr;
        return dummy_records;
    END DUMMYFUNCTION;

调用语句 #

Select 
     * 
From table(DUMMYFUNCTION(p_id_name=>'ID', p_column_name=>'COLUMN1', p_tablename=>'TEST_TABLE', p_type=>'ODCIVARCHAR2LIST', p_delimter=>':'))

错误原因 #

1. 集合类型不匹配：Multiset子查询返回的Level是数值类型，但sys.ODCIVARCHAR2LIST是字符串集合类型，直接转换会触发数据类型不一致错误。
2. 动态SQL转义错误：代码中使用了HTML转义字符>，而非SQL原生的>，导致SQL语法解析异常，间接引发类型兼容问题。
3. 游标收集逻辑缺陷：Exit When csr%NOTFOUND的位置错误，可能导致最后一批数据被遗漏，同时影响结果集的类型匹配逻辑。

解决建议 #

1. 修正集合类型转换 #

将Level转为字符串类型后再存入sys.ODCIVARCHAR2LIST，修改动态SQL子查询部分：

Multiset( Select TO_CHAR(Level) From dual Connect by instr(' || p_column_name || ','':'', 1, Level - 1) > 0 ) as sys.ODCIVARCHAR2LIST

2. 修复动态SQL转义字符 #

把>替换为SQL原生的>，确保语法正确：

Connect by instr(' || p_column_name || ','':'', 1, Level - 1) > 0

3. 优化游标批量收集逻辑 #

调整循环退出条件，保证所有数据被正确收集：

Open csr For sql_qry;
Fetch csr Bulk collect into dummy_records Limit 3;
While csr%FOUND Loop
    Fetch csr Bulk collect into dummy_records Limit 3;
End Loop;
Close csr;

完整修正后的函数代码 #

FUNCTION DUMMYFUNCTION(
    p_id_name             in VARCHAR2,
    p_column_name         in VARCHAR2,
    p_tablename           in VARCHAR2,
    p_type                in VARCHAR2,
    p_delimter            in VARCHAR2
)
return DUMMY_TABLE_TEST

as 

sql_qry     VARCHAR2(4000);
csr         SYS_REFCURSOR;  
dummy_records DUMMY_TABLE_TEST;

BEGIN   

    sql_qry := 'Select DUMMY_RECORD_TEST(' || p_id_name || ',
      trim(regexp_substr(' || p_column_name || ',''[^' || p_delimter || ']+'', 1, lines.column_value)))
        From ' || p_tablename || ',
             Table (CAST (Multiset( Select 
                                        TO_CHAR(Level) 
                                    From dual 
                                    Connect by instr(' || p_column_name || ',''' || p_delimter || ''', 1, Level - 1) > 0
                        ) as sys.ODCIVARCHAR2LIST 
                    )
                ) lines';

    Open csr For sql_qry;
    Fetch csr Bulk collect into dummy_records Limit 3;
    While csr%FOUND Loop
        Fetch csr Bulk collect into dummy_records Limit 3;
    End Loop;
    Close csr;
    return dummy_records;
END DUMMYFUNCTION;

额外优化点 #

• 移除fetch first 1 rows only限制，确保拆分所有行的分隔符内容。
• 将硬编码的分隔符:替换为参数p_delimter，提升函数灵活性。

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