Remove trailing spaces from user_source

S

Sometimes I need to cleanup trailing spaces (RTRIM) from packages, procedures... There are better methods out there, like using proper editor. But this is an approach you can use directly from SQL Developer or console. Remember that source code of views and jobs is stored elsewhere.

DECLARE
    out_clob CLOB;
BEGIN
    DBMS_OUTPUT.PUT_LINE('SET DEFINE OFF');
    DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE STOP');
    DBMS_OUTPUT.PUT_LINE('');
    --
    FOR c IN (
        SELECT DISTINCT s.type, s.name
        FROM user_source s
        WHERE s.type    NOT IN ('TYPE')
            AND s.text  != REGEXP_REPLACE(s.text, '\s+$', '') || CHR(10)
        ORDER BY 1, 2
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('-- ' || c.type || ' ' || c.name);
        DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE ');
        out_clob := 'CREATE OR REPLACE ';
        --
        FOR d IN (
            SELECT REGEXP_REPLACE(s.text, '\s+$', '') AS text
            FROM user_source s
            WHERE s.type    = c.type
                AND s.name  = c.name
            ORDER BY s.line
        ) LOOP
            out_clob := out_clob || d.text || CHR(10);
            DBMS_OUTPUT.PUT_LINE(d.text);
        END LOOP;
        --
        DBMS_OUTPUT.PUT_LINE('/');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('');
        --EXECUTE IMMEDIATE out_clob;
    END LOOP;
END;
/