Dynamic Oracle SQL for Searching All Tables in a Schema for Specific Columns and Generating a Union Query

DECLARE
    -- Column we are looking for in each table of the schema
    COLUMN_TO_FIND VARCHAR2(256);

    -- Column to include so we can filter results for tables 
    -- that also contain this column as well - aka the table
    -- must contain all three columns
    PRIMARY_KEY_1 VARCHAR2(256);
    PRIMARY_KEY_2 VARCHAR2(256);

    -- Filter results to only the specified schemas so we 
    -- aren't querying system tables or other schemas
    SCHEMA_OWNER VARCHAR2(256);

    -- Helper Variable for Table Name in For Loop
    TABLE_NAME VARCHAR2(256);

    -- Helper Variable for Generation of Constant String Value 
    STATIC_COLUMN VARCHAR2(256);

    -- Helper Variable for Generation of Dynamic SQL Statement
    SQL_STATEMENT VARCHAR2(256);
BEGIN   

    -- To prevent buffer overflow
    dbms_output.enable(null);
    
    -- Edit these to your liking - read description above
    COLUMN_TO_FIND := 'DESCRIPTION';
    PRIMARY_KEY_1 := 'PRIMARY_KEY_1';
    PRIMARY_KEY_2 := 'PRIMARY_KEY_2';
    SCHEMA_OWNER := 'MY_COOL_ORACLE_SCHEMA';
    
    -- Loop through the results of the below SQL query to create 
    -- a query that is outputted into DBMS Output
    FOR item IN 
        (
            -- The query that identifies all the tables that contain the specified columns
            WITH COLUMN_TO_FIND_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = COLUMN_TO_FIND and owner = SCHEMA_OWNER), 
                 PRIMARY_KEY_1_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = PRIMARY_KEY_1 and owner = SCHEMA_OWNER), 
                 PRIMARY_KEY_2_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = PRIMARY_KEY_2 and owner = SCHEMA_OWNER)
            SELECT table_name 
            FROM COLUMN_TO_FIND_TABLES 
            WHERE table_name IN (SELECT * FROM PRIMARY_KEY_1_TABLES) AND 
                  table_name IN (SELECT * FROM PRIMARY_KEY_2_TABLES)
        ) 
    LOOP

        -- Redeclare variable to make things cleaner - personal preference
        TABLE_NAME := item.table_name;
    
        -- Declare the table_name as a static column for the union statement 
        -- so we know where the result is coming from if we want to analyze
        -- the specific record that is being outputted via the union
        STATIC_COLUMN := '''' || TABLE_NAME || '''' || ' AS table_name'; 
        
        -- Build the dynamic sql query that will be outputted into DBMS 
        -- so that we can copy it into an SQL worksheet after it finishes
        --
        -- The final query that is built will display all records where "COLUMN_TO_FIND"
        -- has a length greater than 1000 characters
        SQL_STATEMENT := 'select ' || PRIMARY_KEY_1 || ', ' || PRIMARY_KEY_2 || ', ' ||  STATIC_COLUMN || ' from ' || TABLE_NAME || ' where LENGTH(' || COLUMN_TO_FIND || ') > 1000';
        
        -- Output to DBMS Output for copy paste into SQL Worksheet later
        dbms_output.put_line(SQL_STATEMENT);
        dbms_output.put_line('UNION');
    
    END LOOP;
END;