Trawl through an Oracle database for multi-byte characters

check_multi_byte_chars.sql

set serveroutput on;

DECLARE

  sql_stmt        VARCHAR2(32000);
  char_cols_found boolean := false;
  table_name      varchar2(100);
  row_count       number;

  PROCEDURE log (msg_txt in VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(msg_txt);
  END log;

BEGIN

  FOR tab_rec IN (SELECT *
                  FROM   DBA_tables
                  WHERE  1=1
                    AND  owner NOT IN ('SYS','SYSTEM','APEX_050000','OLAPSYS','SCOTT','XDB','WEBUTIL','ORACLE','OJVMSYS','MDSYS'))
  LOOP

    char_cols_found := false;

    sql_stmt := 'SELECT COUNT(*) FROM '||tab_rec.owner||'.'||tab_rec.table_name||' WHERE 1=1 AND (1=2';
    table_name := tab_rec.owner||'.'||tab_rec.table_name;

    FOR col_rec IN (SELECT *
                    FROM   DBA_TAB_COLS
                    WHERE  1=1
                      AND  OWNER         = tab_rec.owner
                      AND  TABLE_NAME    = tab_rec.table_name
                      AND  HIDDEN_COLUMN = 'NO'
                      AND  (DATA_TYPE  = 'VARCHAR2' OR  DATA_TYPE  = 'CHAR'))
    LOOP

      char_cols_found := true;
      sql_stmt := sql_stmt ||' OR LENGTH('||col_rec.column_name||') != LENGTHB('||col_rec.column_name||')';


    END LOOP;

    sql_stmt := sql_stmt || ')';

    IF char_cols_found THEN

      BEGIN

        EXECUTE IMMEDIATE sql_stmt INTO row_count;

        IF row_count > 0 THEN

          log(table_name||','||to_char(row_count));

        END IF;

      EXCEPTION
      WHEN OTHERS THEN

        log(sqlerrm);
        log(sql_stmt);

      END;


    END IF;

  END LOOP;

END;
/

EXIT;