Validate all objects in a schema : Utility Package « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Package

SQL>
SQL> CREATE OR REPLACE PROCEDURE validate_structure (i_schema IN VARCHAR2) IS
  2     v_sql VARCHAR2 (2000);
  3     CURSOR cur_objects (p_schema VARCHAR2) IS
  4        SELECT object_name, object_type FROM sys.dba_objects
  5         WHERE owner = p_schema AND object_type IN ('CLUSTER', 'TABLE')
  6         ORDER BY object_name;
  7  BEGIN
  8     FOR co IN cur_objects (i_schema)
  9     LOOP
 10        v_sql := 'ANALYZE ' ||
 11                 co.object_type ||
 12                 ' ' ||
 13                 i_schema ||
 14                 '.' ||
 15                 co.object_name ||
 16                 ' VALIDATE STRUCTURE CASCADE';
 17        DBMS_UTILITY.exec_ddl_statement (v_sql);
 18        DBMS_OUTPUT.put_line (v_sql);
 19     END LOOP;
 20  END;
 21
 22
 23  --