Oracle基于元查询生成动态SQL执行报错及结果合并问题

• 你自定义的results集合类型是all_tab_columns%rowtype行类型，和动态SQL返回的单个字符字段的结果结构完全不匹配，类型不一致
• dbms_output.put_line仅支持接收字符串、数字等标量类型参数，不能直接传入集合对象，因此触发PLS-00306错误

DECLARE
    sql_total    VARCHAR2(32767) := NULL;
    TYPE rec_type IS RECORD (
        table_name  VARCHAR2(128),
        column_name VARCHAR2(128),
        column_val  VARCHAR2(4000)
    );
    TYPE result_set IS TABLE OF rec_type;
    results_tbl  result_set;
BEGIN
    -- 拼接所有子查询用UNION ALL合并
    FOR i IN (
        SELECT owner, table_name, column_name
        FROM all_tab_columns
        WHERE owner = 'OWNER_XYZ'
          AND UPPER(column_name) LIKE '%COLUMN_XYZ%'
        ORDER BY table_name, column_name
    ) LOOP
        IF sql_total IS NOT NULL THEN
            sql_total := sql_total || ' UNION ALL ';
        END IF;
        sql_total := sql_total 
            || 'SELECT ''' || i.table_name || ''' table_name, '
            || '''' || i.column_name || ''' column_name, '
            || 'DISTINCT ' || i.column_name || ' column_val '
            || 'FROM ' || i.owner || '.' || i.table_name || ' '
            || 'WHERE SUBSTR(' || i.column_name || ',1,1) = ''Y''';
    END LOOP;

    -- 无匹配列时直接返回
    IF sql_total IS NULL THEN
        dbms_output.put_line('未匹配到符合条件的列');
        RETURN;
    END IF;

    -- 执行合并后的SQL，批量收集结果
    EXECUTE IMMEDIATE sql_total BULK COLLECT INTO results_tbl;

    -- 循环打印结果
    dbms_output.put_line('表名|列名|匹配值');
    dbms_output.put_line('---|---|---');
    FOR j IN 1..results_tbl.COUNT LOOP
        dbms_output.put_line(results_tbl(j).table_name || '|' || results_tbl(j).column_name || '|' || results_tbl(j).column_val);
    END LOOP;
END;
/

• 自动将所有匹配表的查询逻辑通过UNION ALL拼接为一条完整SQL，一次性执行得到合并后的结果集
• 结果输出包含表名、列名、匹配到的字段值三个维度，符合预期展示效果
• 增加了无匹配列场景的兼容处理，避免执行异常

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