Generate package spec from the body

S

Sometimes, when I'm working on a new package, it's difficult to keep the specification updated to reflect changes in arguments. Adding new procedures becomes tedious, and when I change the formatting, it's time-consuming to update the specification accordingly. When I reformat a legacy package, I usually copy the whole body into the specification and keep only the declarations. But that also takes some effort.

So I created this nifty script, which generates a specification from your package body. It keeps everything that exists in your current package spec before the first procedure or function (like constants, exceptions, comments...). Everything else is replaced. It does not support forward declarations, and your private procedures will get exposed (which is a good thing, because you should be using the ACCESSIBLE BY clause instead).

Here is the script:

DECLARE
    in_package_name         CONSTANT VARCHAR2(30) := '&PACKAGE_NAME.';
    --
    v_header                VARCHAR2(32767);
    v_package               VARCHAR2(32767);
    v_function              BOOLEAN;
    v_appending             BOOLEAN;
BEGIN
    -- go thru the package spec, to salvage internal constants, exceptions, leading comments...
    FOR s IN (
        SELECT
            s.line,
            REGEXP_REPLACE(RTRIM(RTRIM(UPPER(LTRIM(s.text)), CHR(10))), '\s*--.*$', '') AS text_upper,
            s.text
        FROM user_source s
        WHERE 1 = 1
            AND s.type = 'PACKAGE'
            AND s.name = UPPER(in_package_name)
            AND s.line > 1
    ) LOOP
        -- hide second line if the first line is not complete
        IF s.line = 2 AND s.text_upper IN ('IS', 'AS') THEN
            CONTINUE;
        END IF;

        -- stop when first procedure or function found
        IF (s.text_upper LIKE 'PROCEDURE %' OR s.text_upper LIKE 'FUNCTION %') THEN
            EXIT;   -- break
        END IF;
        --
        v_package := v_package || s.text;
    END LOOP;
    --
    v_package := LTRIM(v_package, CHR(10));

    -- go thru the package body
    FOR s IN (
        SELECT
            s.line,
            REGEXP_REPLACE(RTRIM(RTRIM(UPPER(LTRIM(s.text)), CHR(10))), '\s*--.*$', '') AS text_upper,
            s.text
        FROM user_source s
        WHERE 1 = 1
            AND s.type = 'PACKAGE BODY'
            AND s.name = UPPER(in_package_name)
            AND s.line > 1
        ORDER BY
            s.line
    ) LOOP
        -- find procedure or function start
        IF (s.text_upper LIKE 'PROCEDURE %' OR s.text_upper LIKE 'FUNCTION %') THEN
            v_function  := s.text_upper LIKE 'FUNCTION %';
            v_appending := TRUE;
            v_header    := REGEXP_REPLACE(REGEXP_REPLACE(s.text, '(PROCEDURE)', 'PROCEDURE', 1, 1, 'i'), '(FUNCTION)', 'FUNCTION', 1, 1, 'i');
            v_header    := REGEXP_REPLACE(v_header, '(\s+AS|\s+IS)', '', 1, 1, 'i');
            --
            IF s.text_upper LIKE 'PROCEDURE %' AND s.text NOT LIKE '%(%' THEN
                v_package   := v_package || RTRIM(RTRIM(v_header, CHR(10))) || ';' || CHR(10) || CHR(10) || CHR(10) || CHR(10);
                v_header    := '';  -- shortloop next block
            END IF;
            CONTINUE;
        END IF;

        -- find arguments
        IF v_header IS NOT NULL THEN
            -- attach other statements before first AS/IS
            IF (s.text_upper LIKE '% AS' OR s.text_upper LIKE '% IS') THEN
                s.text          := REGEXP_REPLACE(RTRIM(s.text, CHR(10)), '^(.*)(\s+AS|\s+IS)(\s*--.*)$', '\1');
                v_header        := v_header || s.text;  -- append to the header
                s.text_upper    := 'AS';  -- to trigger next block
            END IF;
            --
            IF s.text_upper IN ('AS', 'IS') THEN
                v_header    := REGEXP_REPLACE(RTRIM(RTRIM(v_header, CHR(10))), '(\s+AS|\s+IS)', '', 1, 1, 'i') || ';' || CHR(10);
                v_package   := v_package || v_header || CHR(10) || CHR(10) || CHR(10);
                v_header    := '';
                v_appending := FALSE;
                CONTINUE;
            END IF;

            -- keep appending
            v_header := v_header || s.text;
        END IF;
    END LOOP;

    -- generate package spec
    IF v_package IS NOT NULL THEN
        v_package :=
            'CREATE OR REPLACE PACKAGE ' || LOWER(in_package_name) || ' AS' || CHR(10) || CHR(10) ||
            RTRIM(v_package, CHR(10)) || CHR(10) || CHR(10) || 'END;';
        --
        DBMS_OUTPUT.PUT_LINE(v_package);
        EXECUTE IMMEDIATE v_package;
    END IF;
END;
/

Don't forget to recompile your invalid objects.