Adding/dropping columns in a repeatable way

O

On one of my projects I have to create patch scripts to add columns to a table or remove them. Thing is, that they might be run multiple times or might get different result on different environments.

So I created this simple script where I can define which column in which table I need to add or drop and it will skip what was already done. Keep in mind that I am not solving changed data types nor the column positions.

PROMPT PROCESSING TABLE CHANGES
--
SET SERVEROUTPUT ON
BEGIN
    FOR c IN (
        WITH x (action, table_name, column_name, data_type) AS (
            SELECT 'ADD',  'TABLE_NAME', 'COLUMN_NAME1', 'NUMBER' FROM DUAL UNION ALL
            SELECT 'DROP', 'TABLE_NAME', 'COLUMN_NAME2', NULL FROM DUAL
        )
        SELECT x.*, c.column_name AS column_exists
        FROM x
        LEFT JOIN user_tab_cols c
            ON c.table_name     = x.table_name
            AND c.column_name   = x.column_name
    ) LOOP
        IF c.action = 'DROP' AND c.column_exists IS NOT NULL THEN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name ||
                ' DROP COLUMN ' || c.column_name;
            DBMS_OUTPUT.PUT_LINE('  DROPPED ' || c.table_name || '.' || c.column_name);
        ELSIF c.action = 'ADD' AND c.column_exists IS NULL THEN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name ||
                ' ADD ' || c.column_name || ' ' || c.data_type;
            DBMS_OUTPUT.PUT_LINE('  ADDED ' || c.table_name || '.' || c.column_name);
        END IF;
    END LOOP;
END;
/

After I change columns I like to move the audit columns to the bottom:

PROMPT MOVE AUDIT COLUMNS
--
DECLARE
    in_table_name       CONSTANT VARCHAR2(30)   := '%';
    in_columns          CONSTANT VARCHAR2(4000) := 'CREATED_BY,CREATED_AT,UPDATED_BY,UPDATED_AT';	-- list audit columns
BEGIN
    FOR t IN (
        SELECT t.table_name
        FROM user_tables t
        LEFT JOIN user_mviews m
            ON m.mview_name     = t.table_name
        WHERE t.table_name      LIKE UPPER(in_table_name) ESCAPE '\'
            AND m.mview_name    IS NULL
    ) LOOP
        FOR c IN (
            WITH x AS (
                SELECT
                    LEVEL AS r#,
                    UPPER(REGEXP_SUBSTR(in_columns, '[^,]+', 1, LEVEL)) AS column_name
                FROM DUAL
                CONNECT BY LEVEL <= REGEXP_COUNT(in_columns, ',') + 1
            )
            SELECT c.table_name, c.column_name
            FROM user_tab_cols c
            JOIN x
                ON x.column_name    = c.column_name
            WHERE c.table_name      = t.table_name
            ORDER BY x.r# ASC
        ) LOOP
            DBMS_OUTPUT.PUT_LINE('  MOVING ' || c.table_name || '.' || c.column_name);
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name || ' MODIFY ' || c.column_name || ' INVISIBLE';
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name || ' MODIFY ' || c.column_name || ' VISIBLE';
        END LOOP;
    END LOOP;
END;
/