在整个数据库中搜索乱码`�` - Oracle

Question

最近，我们通过确保文件为 UTF-8 并且 Java 代码以 UTF-8 编码打开这些文件，解决了字符编码从文本文件错误读入系统的问题。

然而，我们最终在整个数据库表中添加了许多记录，并插入了错误的字符，即 °F 被读取为 �F。因此，即使我们现在已经解决了这个问题，我们现在也需要清理数据库表来纠正这个异常。

谁能建议我实现这一目标的方法吗？

Answer 1

不久前我也遇到了类似的问题。幸运的是，它影响的列数仅限于少数，并且这些列在整个数据库中具有相同的名称。

我通过编写一个执行以下操作的脚本解决了这个问题：

1. 禁用外键约束
2. 建立一个包含以下内容的表列表
   目标列
3. 使用更新列表中的所有表
   REGEXP_REPLACE
4. 提交数据重新启用约束

这使用健康剂量的动态 SQL，从 user_constraints 和 user_tab_columns 中提取数据，过滤我所针对的特定列名称。

这是一个帮助您入门的粗略框架，我只是快速地将其组合在一起，因此没有经过测试。另外，如果您需要担心触发器，您也需要禁用它们：

-- disable constraints
BEGIN
    FOR c IN (
        SELECT c.owner, c.table_name, c.constraint_name, c.constraint_type
        FROM user_constraints c
        INNER JOIN user_tables t ON (t.table_name = c.table_name)
        AND c.status = 'ENABLED'
        AND c.constraint_type NOT IN ('C', 'P')
        ORDER BY c.constraint_type DESC
    )
    LOOP
        dbms_utility.exec_ddl_statement('alter table '||c.table_name||' disable constraint ' || c.constraint_name);
    END LOOP;
END;

-- do the updates
BEGIN
    FOR t IN (
        SELECT table_name, column_name
        FROM user_tab_columns
        WHERE column_name = 'TEMPERATURE'
        AND data_type = 'VARCHAR2';
    )
    LOOP
        dbms_utility.exec_ddl_statement('UPDATE '||t.table_name||' SET ' ||t.column_name||' = '||''GOOD VALUE''||' WHERE '||t.column_name||' = '||''BAD VALUE'');
    END LOOP;
END;

-- re-enable constraints
BEGIN
    FOR c IN (
        SELECT c.owner, c.table_name, c.constraint_name, c.constraint_type
        FROM user_constraints c
        INNER JOIN user_tables t ON (t.table_name = c.table_name)
        AND c.status = 'DISABLED'
        AND c.constraint_type NOT IN ('C', 'P')
        ORDER BY c.constraint_type ASC
    )
    LOOP
        dbms_utility.exec_ddl_statement('alter table '||c.table_name||' enable constraint ' || c.constraint_name);
    END LOOP;
END;
/