Call procedure with aligned args

W

When you need to generate aligned function or procedure call, you can use the following script.

I would like to use data types used in source code, but that is a bit more complex. This should had been my #JoelKallmanDay post, but I could not finish this in time.

WITH inputs AS (
    SELECT
        UPPER('&PROCEDURE_NAME') || '%'     AS procedure_name,
        UPPER('&OBJECT_NAME') || '%'        AS package_name,
        1                                   AS overload
    FROM DUAL
),
a AS (
    SELECT
        a.argument_name,
        a.object_name,
        a.package_name,
        a.position,
        a.in_out,
        a.data_type,
        a.data_length,
        a.data_precision,
        a.defaulted,
        a.pls_type,
        a.char_length,
        a.char_used
    FROM inputs i
    JOIN user_arguments a
        ON a.object_name        LIKE i.procedure_name
        AND a.package_name      LIKE i.package_name
        AND (a.overload         = i.overload OR a.overload IS NULL)
),
s AS (
    SELECT
        5                               AS minimal_space,
        4                               AS tabulator_width,
        --
        MAX(LENGTH(a.argument_name))    AS name_length,
        MIN(a.object_name)              AS procedure_name,
        MIN(a.package_name)             AS package_name,
        MAX(a.position)                 AS last_position
    FROM a
),
t AS (
    SELECT
        s.*,
        CEIL((NVL(LENGTH('    '), 0) + s.minimal_space + s.name_length) / s.tabulator_width) * s.tabulator_width AS set_length
    FROM s
)
SELECT
    MAX(CASE WHEN a.argument_name IS NULL THEN 'fn_out := ' END) ||
    MAX(LOWER(LTRIM(a.package_name || '.' || a.object_name, '.'))) || ' (' AS call
FROM a
UNION ALL
SELECT * FROM (
    SELECT
        RPAD('    '  -- PREFIX
            || LOWER(a.argument_name), t.set_length, ' ') ||
        ' => ' ||
        RPAD(LOWER(a.argument_name) || CASE WHEN a.position < s.last_position THEN ',' END, t.set_length, ' ') ||
        --
        '  -- ' || CASE
            WHEN a.data_type LIKE '%CHAR%'  THEN a.data_type || '(' || a.char_length || CASE WHEN a.char_used != 'B' THEN ' CHAR' END || ')'
            WHEN a.data_type = 'NUMBER'     THEN a.data_type || '(' || a.data_length || ')'
            ELSE a.data_type
            END ||
        --
        CASE WHEN a.defaulted = 'N' THEN ' NOT NULL' END AS call
    FROM a
    CROSS JOIN t
    CROSS JOIN s
    WHERE a.argument_name IS NOT NULL
    ORDER BY a.position
)
UNION ALL
SELECT ');' FROM DUAL;