/*
  The name + type results of these queries will be used by the Code Assistant
  if the "Describe Context" option is enabled. After typing 3 or more characters
  the Code Assistant will show a list of matching names.
  Separate multiple queries with semi-colons and use the :schema bind variable
  to restrict names to the currently connected user. 
  In case of an error the query results will be omitted. No error message will
  be displayed.
  Place this file in the PL/SQL Developer installation directory for all users,
  or in the "%APPDATA%\PLSQL Developer" directory for a specific user.
*/
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ object_name, object_type
  from sys.dba_objects o
 where o.owner = :schema
   and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
;
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ object_name, object_type
  from (select  connect_by_root(synonym_name) object_name, a.object_type
          from sys.dba_synonyms s
           CROSS JOIN LATERAL (select o.object_type
                  from sys.dba_objects o
                 where 1 = 1
                   and s.table_owner = o.owner
                   and s.table_name = o.object_name
                   and o.object_type in ('TABLE',
                                         'VIEW',
                                         'PACKAGE',
                                         'TYPE',
                                         'PROCEDURE',
                                         'FUNCTION',
                                         'SEQUENCE')  ) a
         where 1 = 1
           and connect_by_isleaf = 1
           and connect_by_iscycle = 0
         start with s.owner in ('PUBLIC', USER)
        connect by nocycle s.owner = prior s.table_owner
               and s.synonym_name = prior s.table_name)
 where 1 = 1
   and object_type is not null
;
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ db_link as object_name, 'DATABASE LINK' as object_type
  from sys.dba_db_links o
  where o.owner = :schema
     or o.owner = 'PUBLIC'
;

CREATE OR REPLACE PROCEDURE FOO.SP_RECOMPILE_OBJECTS
AS
BEGIN
    BEGIN
        DBMS_UTILITY.COMPILE_SCHEMA(schema => 'VAL', compile_all => FALSE);
        DBMS_UTILITY.COMPILE_SCHEMA(schema => 'APP', compile_all => TRUE);
        DBMS_UTILITY.COMPILE_SCHEMA(schema => 'CC', compile_all => TRUE);
    END;

    BEGIN
        FOR cur_rec IN (
            SELECT
                owner
               ,object_name
               ,object_type
            FROM dba_objects
            WHERE status = 'INVALID'
              AND owner IN ('CC')
              AND object_type = 'PROCEDURE'
            ORDER BY object_type
                ,owner
                ,object_name
        )
        LOOP
            BEGIN
                EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' || cur_rec.owner || '.' || cur_rec.object_name || ' COMPILE';
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line('EXCEPTION : ' || cur_rec.owner || ' : ' || cur_rec.object_name);
            END;
        END LOOP;
    END;

END SP_RECOMPILE_OBJECTS;
/