Table columns generator

W

When you need to generate aligned table column names. Now updated to use in APEX calls (from Interactive Grid or from Forms DML processing) and to verify existing page items.

WITH s AS (
    SELECT
        'in_'                           AS arg_prefix,
        'l_'                            AS var_prefix,
        'rec.'                          AS set_prefix,
        --
        &APEX_APP_ID                    AS apex_app_id,
        &APEX_PAGE_ID                   AS apex_page_id,
        --
        5                               AS minimal_space,
        4                               AS tabulator_width,
        --
        MAX(LENGTH(c.column_name))      AS name_length,
        MAX(c.table_name)               AS table_name
    FROM user_tab_columns c
    WHERE c.table_name                  LIKE '&TABLE_NAME'
),
t AS (
    SELECT
        s.*,
        CEIL((LENGTH(s.var_prefix) + s.minimal_space + s.name_length) / s.tabulator_width) * s.tabulator_width AS var_length,
        CEIL((LENGTH(s.arg_prefix) + s.minimal_space + s.name_length) / s.tabulator_width) * s.tabulator_width AS arg_length,
        CEIL((LENGTH(s.set_prefix) + s.minimal_space + s.name_length) / s.tabulator_width) * s.tabulator_width AS set_length
    FROM s
)
SELECT
    CASE WHEN c.nullable = 'N' THEN 'Y' END AS is_not_null,
    --
    RPAD(t.var_prefix
        || LOWER(c.column_name), t.var_length, ' ')
        || LOWER(c.table_name) || '.' || LOWER(c.column_name) || '%TYPE'
        || ';' AS local_variables,
    --
    RPAD(t.arg_prefix
        || LOWER(c.column_name), t.arg_length, ' ')
        || LOWER(c.table_name) || '.' ||
        CASE WHEN c.nullable = 'N'
            THEN LOWER(c.column_name) || '%TYPE'
            ELSE RPAD(LOWER(c.column_name) || '%TYPE', t.arg_length + 3, ' ') || ':= NULL'
            END
        || ',' AS arguments,
    --
    RPAD(t.set_prefix
        || LOWER(c.column_name), t.set_length, ' ')
        || ':= ' || t.arg_prefix || LOWER(c.column_name) || ';' AS record_set,
    --
    RPAD(t.arg_prefix
        || LOWER(c.column_name), t.arg_length, ' ')
        || '=> :' || i.item_name || ',' AS call
FROM user_tab_columns c
JOIN t
    ON t.table_name = c.table_name
LEFT JOIN apex_application_page_items i
    ON i.application_id     = t.apex_app_id
    AND i.page_id           = t.apex_page_id
    AND i.item_name         = 'P' || i.page_id || '_' || c.column_name
ORDER BY c.column_id;