Searching views

Y

You already faced this issue. You need to change/remove function and you don't know where it is used. Or you need to know if the functions is even used somewhere. Oracle provides us with user_source/all_source views where you can easily search source code of procedures, functions, packages and triggers. But views are tricky, there are no such objects for views. Fortunately you can create your own from view definition stored as LONG in user_views.text column.

Lets create a table which will store the view source lines:

CREATE TABLE user_views_source (
    owner       VARCHAR2(30)        CONSTRAINT nn_user_views_source_owner   NOT NULL,
    name        VARCHAR2(30)        CONSTRAINT nn_user_views_source_name    NOT NULL,
    line        NUMBER(10)          CONSTRAINT nn_user_views_source_line    NOT NULL,
    text        VARCHAR2(2000),
    --
    CONSTRAINT pk_user_views_source
        PRIMARY KEY (owner, name, line)
);
--
COMMENT ON TABLE  user_views_source IS '[CORE] User views source converted to lines';

Then you need to create this procedure to convert view definitions to this table (as lines to mimic user_source view):

CREATE OR REPLACE PROCEDURE rebuild_user_views_source (
    in_owner            all_views.owner%TYPE            := NULL
)
AS
    v_content           VARCHAR2(32767);
    --
    FUNCTION get_user_views_text (
        in_view_name        all_views.view_name%TYPE,
        in_owner            all_views.owner%TYPE        := NULL
    )
    RETURN VARCHAR2 AS
        out_text            all_views.text%TYPE;
    BEGIN
        SELECT v.text INTO out_text
        FROM all_views v
        WHERE v.owner       = NVL(in_owner, USER)
            AND v.view_name = in_view_name;
        --
        RETURN out_text;
    END;
BEGIN
    DELETE FROM user_views_source t
    WHERE t.owner = NVL(in_owner, USER);
    --
    FOR c IN (
        SELECT
            t.owner,
            t.view_name AS name
        FROM all_views t
        WHERE t.owner = NVL(in_owner, USER)
    ) LOOP
        v_content := get_user_views_text(c.name, c.owner);
        --
        INSERT INTO user_views_source (owner, name, line, text)
        SELECT
            c.owner,
            c.name,
            LEVEL AS line,
            REGEXP_SUBSTR(v_content, '^.*$', 1, LEVEL, 'm') AS text
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(v_content, '^.*$', 1, LEVEL, 'm') IS NOT NULL
            AND PRIOR c.name        = c.name
            AND PRIOR SYS_GUID()    IS NOT NULL;
    END LOOP;
END;
/

Usage is simple. Just refresh this table before searching (or on regular/event basis) using the procedure above. And then you can search source code including the views.

BEGIN
    rebuild_user_views_source();
    COMMIT;
END;
/
--
WITH x AS (
    SELECT '&SEARCH' AS search_string
    FROM DUAL
)
--
SELECT
    t.name, 'VIEW' AS type, t.line, t.text
FROM user_views_source t
CROSS JOIN x
WHERE UPPER(t.text) LIKE '%' || UPPER(x.search_string) || '%' ESCAPE '\'
UNION ALL
--
SELECT
    t.name, t.type, t.line, t.text
FROM user_source t
CROSS JOIN x
WHERE UPPER(t.text) LIKE '%' || UPPER(x.search_string) || '%' ESCAPE '\';

Update

You can easily adjust the query to search for multiple words (in the same object but not on different lines). Following query will returns all objects which contains both &FIRST and &SECOND words.

WITH z AS (
    SELECT '&FIRST' AS searching_for
    FROM DUAL
    UNION ALL
    SELECT '&SECOND'
    FROM DUAL
),
x AS (
    SELECT
        z.*,
        ROW_NUMBER() OVER (ORDER BY NULL) AS r#
    FROM z
),
s AS (
    SELECT s.name, 'VIEW' AS type, s.line, s.text, x.r#
    FROM src_views s
    CROSS JOIN x
    WHERE UPPER(s.text) LIKE UPPER('%' || x.searching_for || '%')
    UNION ALL
    --
    SELECT s.name, s.type, s.line, s.text, x.r#
    FROM user_source s
    CROSS JOIN x
    WHERE UPPER(s.text) LIKE UPPER('%' || x.searching_for || '%')
)
SELECT s.*
FROM s
WHERE (s.name, s.type) IN (
    SELECT s.name, s.type
    FROM s
    GROUP BY s.name, s.type
    HAVING COUNT(DISTINCT s.r#) = (SELECT COUNT(*) FROM x)
)
ORDER BY 1, 2, 3;