PL/SQL使用动态PIVOT时出现数据类型不一致错误求助

咱们直接戳中核心问题：你定义的table_model是仅能存储单varchar2(100)元素的关联数组，但动态PIVOT生成的查询返回的是多列结果集——除了基础的nome列，还会为每个scd_local.descricao值生成单独的列，两者的结构完全不匹配，这就是数据类型不一致错误的根源。

举个实际例子，如果scd_local里有'办公室'、'仓库'两个描述值，你的动态查询会变成：

select * from (select doc.nome, loc.descricao from ...) pivot (max(descricao) for descricao in ( '办公室','仓库' ))

这个查询返回的列是nome、'办公室'、'仓库'三列，但你的tabl只能存储单列字符串，自然装不下多列数据，类型不匹配的错误就必然出现了。

解决办法：用动态方式处理可变列结果 #

既然结果集的列数是动态的，静态定义的集合类型没法匹配，咱们可以用两种方式处理：

方案1：用DBMS_SQL遍历动态结果 #

这种方式能灵活处理任意数量的动态列，适合需要逐行解析所有列的场景：

set serveroutput on;
declare
    storage_var clob;
    storage_query clob;
    v_cursor_id integer;
    v_col_count integer;
    v_col_descs dbms_sql.desc_tab;
    v_status integer;
    v_col_value varchar2(100);
    v_nome varchar2(100);
begin
    SELECT DISTINCT LISTAGG('''' || scd_local.descricao || '''',',') WITHIN GROUP (ORDER BY scd_local.descricao) 
    INTO storage_var 
    FROM scd_local;

    storage_query := 'select * from (select doc.nome, loc.descricao from scd_documento doc, scd_local_doc doc_loc, scd_local loc where doc.nome = doc_loc.id_doc and loc.id = doc_loc.id_local order by 1, 2) pivot (max(descricao) for descricao in ( ' || storage_var || ' ))';
    dbms_output.put_line(storage_query);

    -- 初始化DBMS_SQL游标
    v_cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor_id, storage_query, dbms_sql.native);
    dbms_sql.describe_columns(v_cursor_id, v_col_count, v_col_descs);

    -- 为每个列绑定变量
    for i in 1..v_col_count loop
        dbms_sql.define_column(v_cursor_id, i, v_col_value, 100);
    end loop;

    v_status := dbms_sql.execute(v_cursor_id);

    -- 逐行读取并打印结果
    while dbms_sql.fetch_rows(v_cursor_id) > 0 loop
        dbms_sql.column_value(v_cursor_id, 1, v_nome);
        dbms_output.put('文档名称: ' || v_nome);
        -- 遍历打印所有动态列
        for i in 2..v_col_count loop
            dbms_sql.column_value(v_cursor_id, i, v_col_value);
            dbms_output.put(' | ' || v_col_descs(i).col_name || ': ' || v_col_value);
        end loop;
        dbms_output.new_line;
    end loop;

    dbms_sql.close_cursor(v_cursor_id);
end;
/

方案2：合并动态列为单列存入集合 #

如果你的需求只是把每一行的所有列合并成单个字符串存储，可以修改动态查询，用字符串拼接把所有列合并成一列，这样就能匹配你的集合类型：

set serveroutput on;
declare
    storage_var clob;
    storage_cols clob;
    storage_query clob;
    type table_model is table of varchar2(1000) index by pls_integer; -- 扩大存储长度
    tabl table_model;
begin
    -- 获取不带引号的列名列表，用于拼接
    SELECT DISTINCT LISTAGG(scd_local.descricao,',') WITHIN GROUP (ORDER BY scd_local.descricao) 
    INTO storage_cols 
    FROM scd_local;
    -- 获取带引号的IN子句列表
    SELECT DISTINCT LISTAGG('''' || scd_local.descricao || '''',',') WITHIN GROUP (ORDER BY scd_local.descricao) 
    INTO storage_var 
    FROM scd_local;

    -- 构造合并列的查询
    storage_query := 'select nome || '' | '' || ' || REPLACE(storage_cols, ',', ' || '' | '' || ') || ' as combined_row from (select doc.nome, loc.descricao from scd_documento doc, scd_local_doc doc_loc, scd_local loc where doc.nome = doc_loc.id_doc and loc.id = doc_loc.id_local order by 1, 2) pivot (max(descricao) for descricao in ( ' || storage_var || ' ))';
    dbms_output.put_line(storage_query);

    execute immediate storage_query bulk collect into tabl;

    -- 打印结果
    for i in 1.. tabl.count loop
        dbms_output.put_line(tabl(i));
    end loop;
end;
/

额外提醒 #

1. 动态SQL拼接时要注意特殊字符问题，如果scd_local.descricao包含单引号，建议用DBMS_ASSERT.ENQUOTE_LITERAL函数转义，避免语法错误。
2. PIVOT生成的列名是带单引号的字符串，后续处理时要注意引号的转义逻辑。

内容的提问来源于stack exchange，提问作者Jean Willian S. J.