View generator (with joins)

W

When creating page in APEX I need a view for an object (table/view) I would like to present on page in report/grid. This is very tiring. So I created this simple generator. You just need to pass it a list of tables/view you would like to use (and join) in your new view.

This will help you to create the list if you need to use more then one table/view. Just change the order of the tables and aliases to your needs. Joins will be done in this order.

WITH x AS (
    SELECT '%'      AS tables_like FROM DUAL UNION ALL
    SELECT '%'      AS tables_like FROM DUAL
)
SELECT LISTAGG(t.table_name || ' ' || t.table_alias, ',') WITHIN GROUP (ORDER BY t.table_name) AS tables
FROM (
    SELECT
        t.table_name,
        CHR(96 + ROW_NUMBER() OVER (ORDER BY t.table_name)) AS table_alias
    FROM x
    JOIN user_tables t
        ON t.table_name     LIKE x.tables_like
        AND t.table_name    NOT LIKE '%$'
    GROUP BY t.table_name
) t;

Now pass list of tables created above and you will see the view definition including all joins needed. Columns with same name will be commented out so if you need them you can uncomment them and add them a unique alias. You might need to check join conditions, maybe remove some, but you should have everything ready there.

DECLARE
    in_tables       CONSTANT VARCHAR2(2000) := '&LIST_OF_TABLES_WITH_ALIASES';
    in_view_name    CONSTANT VARCHAR2(2000) := '&NEW_VIEW_NAME';
    --
    passed_cols     VARCHAR2(32767) := '|';
    max_col_size    PLS_INTEGER;
    --
    CURSOR list_tables (
        in_tables       VARCHAR2
    ) IS
        SELECT
            x.table_name,
            x.table_alias,
            ROW_NUMBER() OVER (ORDER BY x.table_order)                                  AS table_order
        FROM user_tables t
        JOIN (
            SELECT
                REGEXP_REPLACE(REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL), '\s.*', '') AS table_name,
                REGEXP_REPLACE(REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL), '.*\s', '') AS table_alias,
                LEVEL                                                                   AS table_order
            FROM DUAL
            CONNECT BY REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL) IS NOT NULL
        ) x
            ON t.table_name     = UPPER(x.table_name)
            AND t.table_name    NOT LIKE '%$';
    --
    CURSOR list_columns (
        in_tables       VARCHAR2
    ) IS
        SELECT
            t.table_alias,
            c.column_name
        FROM (
            SELECT x.*
            FROM user_tables t
            JOIN (
                SELECT
                    REGEXP_REPLACE(REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL), '\s.*', '') AS table_name,
                    REGEXP_REPLACE(REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL), '.*\s', '') AS table_alias,
                    LEVEL                                                                   AS table_order
                FROM DUAL
                CONNECT BY REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL) IS NOT NULL
            ) x
                ON t.table_name     = UPPER(x.table_name)
                AND t.table_name    NOT LIKE '%$'
        ) t
        JOIN user_tab_columns c
            ON c.table_name     = t.table_name
        ORDER BY t.table_order, c.column_id;
    --
    CURSOR list_constraints (
        in_table_name       VARCHAR2,
        in_tables           VARCHAR2
    ) IS
        SELECT
            p.column_name,
            x.table_alias,
            r.column_name AS parent_column,
            r.position,
            MAX(LENGTH(p.column_name)) OVER() AS max_col_size
        FROM user_constraints n
        JOIN user_cons_columns p
            ON p.constraint_name    = n.constraint_name
        JOIN user_cons_columns r
            ON r.constraint_name    = n.r_constraint_name
            AND r.position          = p.position
        JOIN (
            SELECT x.*
            FROM user_tables t
            JOIN (
                SELECT
                    REGEXP_REPLACE(REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL), '\s.*', '') AS table_name,
                    REGEXP_REPLACE(REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL), '.*\s', '') AS table_alias,
                    LEVEL                                                                   AS table_order
                FROM DUAL
                CONNECT BY REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL) IS NOT NULL
            ) x
                ON t.table_name     = UPPER(x.table_name)
                AND t.table_name    NOT LIKE '%$'
        ) x
            ON x.table_name         = r.table_name
        WHERE n.constraint_type     = 'R'
            AND n.table_name        = in_table_name
        ORDER BY r.position;
    --
BEGIN
    IF in_view_name IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE VIEW ' || LOWER(in_view_name) || ' AS');
    END IF;

    -- create select columns
    DBMS_OUTPUT.PUT_LINE('SELECT');
    --
    FOR c IN list_columns(in_tables) LOOP
        DBMS_OUTPUT.PUT_LINE(
            '    ' ||
            CASE WHEN passed_cols LIKE '%|' || c.column_name || '|%' THEN '-- ' END ||
            c.table_alias || '.' || LOWER(c.column_name) || ','
        );
        --
        passed_cols := passed_cols || c.column_name || '|';
    END LOOP;

    -- calculate column size for join alignment
    SELECT MAX((FLOOR(LENGTH(c.column_name) / 4) + 1) * 4) INTO max_col_size
    FROM user_tab_columns c
    JOIN (
        SELECT
            UPPER(REGEXP_REPLACE(REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL), '\s.*', '')) AS table_name
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(in_tables, '[^,]+', 1, LEVEL) IS NOT NULL
    ) x
        ON c.table_name     LIKE x.table_name
        AND c.table_name    NOT LIKE '%$';

    -- continue with joins
    FOR c IN list_tables(in_tables) LOOP
        DBMS_OUTPUT.PUT_LINE(
            CASE WHEN c.table_order = 1 THEN 'FROM' ELSE 'JOIN' END ||
            ' ' || LOWER(c.table_name) || ' ' || c.table_alias
        );
        --
        IF c.table_order > 1 THEN
            FOR r IN list_constraints(c.table_name, in_tables) LOOP
                DBMS_OUTPUT.PUT_LINE(
                    '    ' ||
                    CASE WHEN r.position = 1 THEN 'ON' ELSE 'AND' END ||
                    ' ' || c.table_alias || '.' || RPAD(LOWER(r.column_name), max_col_size + 3 + CASE WHEN r.position = 1 THEN 1 ELSE 0 END) ||
                    ' = ' || r.table_alias || '.' || LOWER(r.parent_column)
                );
            END LOOP;
        END IF;
    END LOOP;
    --
    DBMS_OUTPUT.PUT_LINE(';');
END;
/