Convert LONG content to lines

S

Searching views source code is very difficult due to usage of prehistoric LONG data type. This script converts views to similar structure as user_source view.

Let's create target table first:

--DROP TABLE src_views;
CREATE TABLE src_views (
    name    VARCHAR2(30)        NOT NULL,
    line    NUMBER(8)           NOT NULL,
    text    VARCHAR2(4000),
    --
    CONSTRAINT pk_src_views
        PRIMARY KEY (name, line)
);

Run the script:

DECLARE
    in_view_name        CONSTANT VARCHAR2(30) := '%';
    --
    PROCEDURE save_line_to_table (
        in_name         src_views.name%TYPE,
        in_line         src_views.line%TYPE,
        in_text         src_views.text%TYPE
    ) AS
    BEGIN
        INSERT INTO src_views (name, line, text)
        VALUES (
            in_name,
            in_line,
            in_text
        );
    END;
    --
    PROCEDURE clob_to_lines (
        in_name         VARCHAR2,
        in_clob         CLOB
    ) AS
        clob_len        PLS_INTEGER     := DBMS_LOB.GETLENGTH(in_clob);
        clob_line       PLS_INTEGER     := 1;
        offset          PLS_INTEGER     := 1;
        amount          PLS_INTEGER     := 32767;
        buffer          VARCHAR2(32767);
    BEGIN
        WHILE offset < clob_len LOOP
            IF INSTR(in_clob, CHR(10), offset) = 0 THEN
                amount := clob_len - offset + 1;
            ELSE
                amount := INSTR(in_clob, CHR(10), offset) - offset;
            END IF;
            --
            IF amount = 0 THEN
                buffer := '';
            ELSE
                DBMS_LOB.READ(in_clob, amount, offset, buffer);
            END IF;
            --
            save_line_to_table (
                in_name     => in_name,
                in_line     => clob_line,
                in_text     => REPLACE(REPLACE(buffer, CHR(13), ''), CHR(10), '')
            );
            --
            clob_line := clob_line + 1;
            IF INSTR(in_clob, CHR(10), offset) = clob_len THEN
                buffer := '';
            END IF;
            offset := offset + amount + 1;
        END LOOP;
    END;
BEGIN
    DELETE FROM src_views v
    WHERE v.name LIKE in_view_name;
    --
    FOR c IN (
        SELECT v.view_name, DBMS_METADATA.GET_DDL('VIEW', v.view_name) AS content
        FROM user_views v
        WHERE v.view_name LIKE in_view_name
        ORDER BY 1
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(c.view_name);
        clob_to_lines(c.view_name, REGEXP_REPLACE(c.content, '^(\s*)', ''));
    END LOOP;
END;
/

Check results:

SELECT * FROM src_views ORDER BY 1, 2;

If you need LONG content as CLOB, you can use this neat trick:

--DROP TABLE src_views_clob;
CREATE TABLE src_views_clob (
    name        VARCHAR2(30)        NOT NULL,
    content     CLOB,
    --
    CONSTRAINT pk_src_views_clob
        PRIMARY KEY (name)
);
--
INSERT INTO src_views_clob
SELECT view_name, TO_LOB(text) AS content
FROM user_views;