F
A few years back, I created a view generator with support for JOINs. Unfortunately, it did not work for many use cases. It was bothering me for a while, so I decided to fix it.
You list several tables with your preferred aliases, and it will find foreign keys between the tables (both ways) and apply them to the previous tables. So the table order is critical. I tried to adjust the table order based on the constraints, but that proved to be a bit challenging, so at least for now you have to figure out the table order yourself. Also, if you have columns with the same name, the later ones will be commented out.
DECLARE
in_tables CONSTANT VARCHAR2(4000) := '
TABLE_NAME1 t1,
TABLE_NAME2 t2,
TABLE_NAME3 t3,
';
in_ignore_cols CONSTANT VARCHAR2(4000) := 'CREATED_BY,CREATED_AT,UPDATED_BY,UPDATED_AT,';
--
v_tables VARCHAR2(4000);
v_processed VARCHAR2(4000);
v_line VARCHAR2(4000);
v_max_col PLS_INTEGER;
--
FUNCTION replace_table_aliases (
in_string VARCHAR2,
in_tables VARCHAR2,
in_max_col PLS_INTEGER
)
RETURN VARCHAR2
AS
v_out VARCHAR2(4000) := in_string;
BEGIN
-- replace table names with aliases from source
FOR c IN (
SELECT
REGEXP_REPLACE(TRIM(t.column_value), '^.* ', '') AS table_alias,
UPPER(REGEXP_REPLACE(TRIM(t.column_value), ' .*$', '')) AS table_name
FROM APEX_STRING.SPLIT(in_tables, ',') t
) LOOP
v_out := REPLACE(v_out, c.table_name || '.', c.table_alias || '.');
END LOOP;
-- align columns
v_out := REPLACE(v_out,
REGEXP_SUBSTR(v_out, '^([^=]+)'),
RPAD(REGEXP_SUBSTR(v_out, '^([^=]+)'), in_max_col + 4)
);
--
RETURN v_out;
END;
--
BEGIN
v_tables := TRIM(',' FROM TRIM(REGEXP_REPLACE(REPLACE(in_tables, CHR(10), ','), '\s+', ' ')));
--
DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE VIEW .._v AS');
DBMS_OUTPUT.PUT_LINE('SELECT');
-- list columns for requested tables
-- duplicate column names will be commented out so you can decide what to do with them
FOR c IN (
WITH t AS (
SELECT
ROWNUM AS table_pos,
REGEXP_REPLACE(TRIM(t.column_value), '^.* ', '') AS table_alias,
UPPER(REGEXP_REPLACE(TRIM(t.column_value), ' .*$', '')) AS table_name
FROM APEX_STRING.SPLIT(v_tables, ',') t
),
c AS (
SELECT
t.*, c.column_name, c.column_id
FROM t
JOIN user_tab_columns c
ON c.table_name = t.table_name
)
SELECT
c.table_pos,
c.table_name,
c.table_alias,
c.column_name,
c.column_id,
--
NULLIF(',' || LISTAGG(f.table_name, ',') || ',', ',,') AS dupe_tables,
--
CASE
WHEN LEAD(c.column_id) OVER(ORDER BY c.table_pos, c.column_id) IS NULL THEN 'Y'
WHEN LEAD(c.column_id) OVER(ORDER BY c.table_pos, c.column_id) = 1 THEN 'T'
END AS last_column
--
FROM c
LEFT JOIN c f
ON f.table_name != c.table_name
AND f.column_name = c.column_name
AND c.table_pos > 1
WHERE INSTR(',' || in_ignore_cols || ',', ',' || c.column_name || ',') = 0
GROUP BY ALL
ORDER BY
c.table_pos,
c.column_id
) LOOP
DBMS_OUTPUT.PUT_LINE(
' '
|| CASE WHEN c.dupe_tables IS NOT NULL THEN '-- ' END
|| c.table_alias || '.' || LOWER(c.column_name)
|| CASE WHEN NVL(c.last_column, 'N') != 'Y' THEN ',' END
);
--
IF c.last_column IN ('T', 'Y') THEN
DBMS_OUTPUT.PUT_LINE(' --');
END IF;
END LOOP;
-- get maximum length of column names for better alignment
WITH t AS (
SELECT
UPPER(REGEXP_REPLACE(TRIM(t.column_value), ' .*$', '')) AS table_name
FROM APEX_STRING.SPLIT(v_tables, ',') t
)
SELECT FLOOR(MAX(LENGTH(c.column_name)) / 4) * 4 + 4
INTO v_max_col
FROM user_constraints r
JOIN t
ON t.table_name = r.table_name
JOIN user_cons_columns c
ON c.constraint_name = r.constraint_name
WHERE 1 = 1
AND r.constraint_type IN ('P', 'R')
AND r.status = 'ENABLED';
-- generate join statements
FOR x IN (
SELECT
ROWNUM AS table_pos,
REGEXP_REPLACE(TRIM(t.column_value), '^.* ', '') AS table_alias,
UPPER(REGEXP_REPLACE(TRIM(t.column_value), ' .*$', '')) AS table_name
FROM APEX_STRING.SPLIT(v_tables, ',') t
) LOOP
DBMS_OUTPUT.PUT_LINE(
CASE WHEN x.table_pos = 1 THEN 'FROM ' ELSE 'JOIN ' END
|| LOWER(x.table_name) || ' ' || x.table_alias
);
--
v_processed := v_processed || x.table_name || ' ' || x.table_alias || ', ';
--
IF x.table_pos > 1 THEN
FOR c IN (
WITH t AS (
SELECT /*+ MATERIALIZE */
ROWNUM AS table_pos,
UPPER(REGEXP_REPLACE(TRIM(t.column_value), ' .*$', '')) AS table_name
FROM APEX_STRING.SPLIT(v_processed, ',') t
)
SELECT
q.constraint_name,
q.position,
CASE WHEN q.left_col LIKE x.table_name || '.%'
THEN q.left_col || ' = ' || q.right_col
ELSE q.right_col || ' = ' || q.left_col
END AS where_filter
--
FROM (
SELECT
r.table_name,
p.constraint_name,
--
g.table_name || '.' || LOWER(g.column_name) AS left_col,
c.table_name || '.' || LOWER(c.column_name) AS right_col,
t.table_pos,
g.position,
GREATEST(LENGTH(g.column_name), LENGTH(c.column_name)) AS max_col
--
FROM user_constraints r
JOIN user_constraints p
ON p.r_constraint_name = r.constraint_name
AND p.constraint_type = 'R'
AND p.status = 'ENABLED'
JOIN t
ON t.table_name = r.table_name
JOIN user_cons_columns c
ON c.constraint_name = p.constraint_name
JOIN user_cons_columns g
ON g.constraint_name = p.r_constraint_name
AND g.position = c.position
WHERE r.constraint_type = 'P' -- P,U
AND r.status = 'ENABLED'
AND r.table_name != p.table_name
AND p.table_name = x.table_name
--
UNION ALL
--
SELECT
p.table_name,
r.constraint_name,
--
g.table_name || '.' || LOWER(g.column_name) AS left_col,
c.table_name || '.' || LOWER(c.column_name) AS right_col,
t.table_pos,
g.position,
GREATEST(LENGTH(g.column_name), LENGTH(c.column_name)) AS max_col
--
FROM user_constraints r
JOIN user_constraints p
ON p.constraint_name = r.r_constraint_name
AND p.constraint_type = 'P' -- P,U
AND p.status = 'ENABLED'
JOIN t
ON t.table_name = r.table_name
JOIN user_cons_columns c
ON c.constraint_name = r.constraint_name
JOIN user_cons_columns g
ON g.constraint_name = r.r_constraint_name
AND g.position = c.position
--
WHERE r.constraint_type = 'R'
AND r.status = 'ENABLED'
AND r.table_name != p.table_name
AND p.table_name = x.table_name
) q
ORDER BY
table_pos,
position
) LOOP
v_line := replace_table_aliases(c.where_filter, v_tables, v_max_col);
--
DBMS_OUTPUT.PUT_LINE(' '
|| CASE WHEN c.position = 1 THEN 'ON ' ELSE 'AND ' END
|| CASE WHEN c.position = 1 THEN REPLACE(v_line, ' =', ' =') ELSE v_line END
);
END LOOP;
END IF;
END LOOP;
--
DBMS_OUTPUT.PUT_LINE(';');
END;
/
You can explore the table relations article to find out relations in between your tables.
Comments
Post a Comment