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;
Comments
Post a Comment