Find an APEX Component ID in all Oracle APEX Dictionary Views

OK Pretty niche, but if you ever want to find which columns in which views an APEX Component ID resides in, here’s a brute force approach for you:

DECLARE
    l_id NUMBER := 7128208610686091; -- Change to your component ID
    l_sql VARCHAR2(4000);
    l_count NUMBER;
BEGIN
    FOR rec IN (
        SELECT DISTINCT
            ad.apex_view_name,
            c.column_name
        FROM 
            apex_dictionary ad
            JOIN all_tab_columns c ON ad.apex_view_name = c.table_name
        WHERE 
            ad.column_id = 0
            AND c.column_name LIKE '%\_ID' ESCAPE '\'
    ) LOOP
        BEGIN
            l_sql := 'SELECT COUNT(*) FROM ' || rec.apex_view_name ||
                    ' WHERE ' || rec.column_name || ' = :1';

            EXECUTE IMMEDIATE l_sql INTO l_count USING l_id;

            IF l_count > 0 THEN
                DBMS_OUTPUT.PUT_LINE('Found in ' || rec.apex_view_name || 
                                    ' (column: ' || rec.column_name || ')');
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                CONTINUE;  -- Skip views/columns with access issues or type mismatches
        END;
    END LOOP;
END;

Results (in 80 seconds) are:

Found in APEX_APPLICATION_PAGE_REGIONS (column: BREADCRUMB_ID)
Found in APEX_APPLICATION_BC_ENTRIES (column: BREADCRUMB_ID)
Found in APEX_APPLICATION_BREADCRUMBS (column: BREADCRUMB_ID)

Statement processed.

80.82 seconds

ENJOY!

Whats the picture? a bubble under the ice, looks a bit like me 😛