Fix inconsistent data types

A

Another issue I faced on each project is data type inconsistency. In this older article I mentioned how to fix data types mismatches on keys. Today, I show you a script, which fix it for you (based on a column name match).

Let's explore this issue on the audit columns (who created or updated the row and when). To check the audit columns for user:

SELECT
    c.column_name,
    c.data_type,
    c.char_used,
    c.data_length,
    c.nullable,
    c.data_default_vc,
    COUNT(*) AS count_tables
FROM user_tab_cols c
JOIN user_tables t
    ON t.table_name     = c.table_name
WHERE c.table_name      LIKE '%'
    AND c.column_name   LIKE 'CREATED_BY'
GROUP BY
    c.column_name,
    c.data_type,
    c.char_used,
    c.data_length,
    c.nullable,
    c.data_default_vc
ORDER BY 1, 2, 3, 4;

Let's check the audit column for date. This gets more trickier, because we might not have consistent names. To keep the changes low, I am not changing the column names, just consolidating data types.

SELECT
    c.column_name,
    c.data_type,
    c.char_used,
    c.data_length,
    c.nullable,
    c.data_default_vc,
    COUNT(*) AS count_tables
FROM user_tab_cols c
JOIN user_tables t
    ON t.table_name     = c.table_name
WHERE c.table_name      LIKE '%'
    AND c.column_name   IN ('CREATED_ON', 'CREATED_AT')
GROUP BY
    c.column_name,
    c.data_type,
    c.char_used,
    c.data_length,
    c.nullable,
    c.data_default_vc
ORDER BY 1, 2, 3, 4;

So now you can see how good or bad is your project. If you have any inconsistencies, you can fix them with this script (modify part in the moddle for your data types):

BEGIN
    FOR c IN (
        SELECT
            c.table_name,
            c.column_name,
            c.data_type,
            c.char_used,
            c.data_length,
            c.nullable,
            c.data_default_vc
        FROM user_tab_cols c
        JOIN user_tables t
            ON t.table_name     = c.table_name
        WHERE c.table_name      LIKE '%'
            AND c.column_name   IN ('CREATED_BY', 'CREATED_AT', 'CREATED_ON')
        ORDER BY 1, 2
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('PROCESSING: ' || c.table_name || '.' || c.column_name);

        -- disable table triggers
        FOR d IN (
            SELECT t.trigger_name
            FROM user_triggers t
            WHERE t.table_name = c.table_name
        ) LOOP
            EXECUTE IMMEDIATE
                'ALTER TRIGGER ' || d.trigger_name || ' DISABLE';
        END LOOP;

        -- we will be creating a column copy, so make sure we remove the one from previous run (if exists)
        BEGIN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name ||
                ' DROP COLUMN ' || c.column_name || '__';
        EXCEPTION
        WHEN OTHERS THEN
            NULL;
        END;

        --
        -- MODIFY THIS PART TO SET THE TYPE YOU WANT
        --
        IF c.column_name = 'CREATED_BY' THEN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name ||
                ' ADD ' || c.column_name || '__ VARCHAR2(128) DEFAULT SYS_CONTEXT(''APEX$SESSION'',''APP_USER'')';
            EXECUTE IMMEDIATE
                'UPDATE ' || c.table_name ||
                ' SET ' || c.column_name || '__ = NVL(' || c.column_name || ', USER)';
        END IF;
        --
        IF c.column_name IN ('CREATED_AT', 'CREATED_ON') THEN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name ||
                ' ADD ' || c.column_name || '__ DATE DEFAULT SYSDATE';
            EXECUTE IMMEDIATE
                'UPDATE ' || c.table_name ||
                ' SET ' || c.column_name || '__ = NVL(' || c.column_name || ', SYSDATE)';
        END IF;

        -- make created* columns mandatory
        IF c.column_name LIKE 'CREATED%' THEN
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name ||
            ' MODIFY ' || c.column_name || '__ NOT NULL';
        END If;

        -- remove original column, for big tables we should use UNUSED feature
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name ||
            ' DROP COLUMN ' || c.column_name;
        --
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name ||
            ' RENAME COLUMN ' || c.column_name || '__ TO ' || c.column_name;

        -- enable table triggers again
        FOR d IN (
            SELECT t.trigger_name
            FROM user_triggers t
            WHERE t.table_name = c.table_name
        ) LOOP
            EXECUTE IMMEDIATE
                'ALTER TRIGGER ' || d.trigger_name || ' ENABLE';
        END LOOP;
    END LOOP;
    --
    DBMS_UTILITY.COMPILE_SCHEMA(schema => USER);
END;
/

After you are done, you have to recompile invalid objects, sync your regions in APEX to update for new data types and you also might need to modify your audit triggers.