PL/SQL find dependencies in other objects (v2)

Standard

I found that my prior code did not account for dependencies in views. Dammit, but there must be a way. I found it, and wrapped it up into one  semi-organized chunk of code for audit purposes. As always, let me know what you think.

--FIRST WE NEED TO DROP THE TABLES SO WE CAN USE THEM AGAIN.
DROP TABLE TEMP_DEPENDENCIES;
DROP TABLE TEMP_OBJECTS;

--NOW WE NEED TO MAKE SURE THEY ARE SET UP PROPERLY
CREATE TABLE TEMP_DEPENDENCIES (
 OBJECT_OWNER VARCHAR(30),
 OBJECT_TYPE VARCHAR(30),
 OBJECT_NAME VARCHAR(30),
 DEPENDANT_OWNER VARCHAR(30),
 DEPENDANT_OBJECT_TYPE VARCHAR(30),
 DEPENDANT_OBJECT VARCHAR(30),
 DEPENDANT_LINE VARCHAR(30)
 );

CREATE TABLE TEMP_OBJECTS (
 OBJECT_OWNER VARCHAR(30),
 OBJECT_TYPE VARCHAR(30),
 OBJECT_NAME VARCHAR(30)
 );

--INSERT ALL OJBECTS OF INTEREST
INSERT INTO TEMP_OBJECTS
WITH OBJECT_OWNERS AS (
 SELECT 'SYS' AS OBJECT_OWNER
 FROM DUAL
 )
SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME
FROM (
 SELECT OWNER AS OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME
 FROM ALL_OBJECTS
 WHERE OWNER IN (
 SELECT OBJECT_OWNER
 FROM OBJECT_OWNERS
 ) MINUS
 SELECT OWNER AS OBJECT_OWNER,
 'TABLE' AS OBJECT_TYPE,
 OBJECT_NAME
 FROM ALL_OBJECTS
 WHERE OWNER IN (
 SELECT OBJECT_OWNER
 FROM OBJECT_OWNERS
 )
 AND OBJECT_TYPE = 'MATERIALIZED VIEW'
 );

COMMIT;

--THIS LOOP LOOKS AT ALL THE DEPENDENCIES IN THE VIEWS
DECLARE L_CHAR VARCHAR2(32767);

BEGIN
 FOR OBJ IN (SELECT * FROM TEMP_OBJECTS)

 LOOP
 FOR REC IN (SELECT * FROM ALL_VIEWS)

 LOOP L_CHAR := REC.TEXT;
 IF (INSTR(L_CHAR, OBJ.OBJECT_NAME) > 0)
 THEN INSERT INTO TEMP_DEPENDENCIES VALUES (OBJ.OBJECT_OWNER,OBJ.OBJECT_TYPE,OBJ.OBJECT_NAME,REC.OWNER,'VIEW',REC.VIEW_NAME,-1); COMMIT;
 END IF;
 END LOOP;
 END LOOP;
END;

--THIS COMBINES THE VIEW DEPENDENCIES WITH ALL OTHER DEPENDENCIES
INSERT INTO TEMP_DEPENDENCIES
SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 DEPENDANT_LINE
FROM TEMP_OBJECTS
INNER JOIN (
 SELECT OWNER AS DEPENDANT_OWNER,
 TYPE AS DEPENDANT_OBJECT_TYPE,
 NAME AS DEPENDANT_OBJECT,
 LINE AS DEPENDANT_LINE,
 TEXT AS DEPENDANT_TEXT
 FROM ALL_SOURCE
 WHERE TEXT NOT LIKE '--%'
 ) DETAILS
 ON INSTR(DETAILS.DEPENDANT_TEXT, TEMP_OBJECTS.OBJECT_NAME) != 0;

COMMIT;

--THIS MAKES SENSE OF THE DEPENDENCIES. IT COMBINES INSTANCES ON MULTIPULE LINES INTO ONE CSV RESULT 
SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 SUBSTR(DEPENDANT_LINE, 2, MAX_LEN_DEPENDANT_LINE - 1) AS DEPENDANT_LINE
FROM (
 SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 LENGTH(SYS_CONNECT_BY_PATH(DEPENDANT_LINE, ',')) AS LEN_DEPENDANT_LINE,
 MAX(LENGTH(SYS_CONNECT_BY_PATH(DEPENDANT_LINE, ','))) OVER (
 PARTITION BY OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT
 ) AS MAX_LEN_DEPENDANT_LINE,
 SYS_CONNECT_BY_PATH(DEPENDANT_LINE, ',') AS DEPENDANT_LINE
 FROM (
 SELECT OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 DEPENDANT_LINE,
 ROW_NUMBER() OVER (
 PARTITION BY OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT ORDER BY OBJECT_OWNER,
 OBJECT_TYPE,
 OBJECT_NAME,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT,
 DEPENDANT_LINE
 ) AS DEPENDANT_OBJECT_LEVEL
 FROM TEMP_DEPENDENCIES
 ) SUBQ START WITH DEPENDANT_OBJECT_LEVEL = 1
 CONNECT BY PRIOR(OBJECT_OWNER || '.' || OBJECT_TYPE || '.' || OBJECT_NAME || '.' || DEPENDANT_OWNER || '.' || DEPENDANT_OBJECT_TYPE || '.' || DEPENDANT_OBJECT || '.' || TO_CHAR(DEPENDANT_OBJECT_LEVEL + 1))
 = (OBJECT_OWNER || '.' || OBJECT_TYPE || '.' || OBJECT_NAME || '.' || DEPENDANT_OWNER || '.' || DEPENDANT_OBJECT_TYPE || '.' || DEPENDANT_OBJECT || '.' || TO_CHAR(DEPENDANT_OBJECT_LEVEL))
 ) CONNECTED_DATA
WHERE LEN_DEPENDANT_LINE = MAX_LEN_DEPENDANT_LINE
ORDER BY OBJECT_OWNER,
 OBJECT_NAME,
 OBJECT_TYPE,
 DEPENDANT_OWNER,
 DEPENDANT_OBJECT_TYPE,
 DEPENDANT_OBJECT
Advertisements

One thought on “PL/SQL find dependencies in other objects (v2)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s