Useful shortcuts for vi editor

Friday 21 October 2016

How to compile invalid objects with custom procedure?

CREATE OR REPLACE PROCEDURE P_COMPILE_INVALID_OBJECTS
   AUTHID CURRENT_USER
IS
   str_exec    VARCHAR2 (200);
   total_inv   NUMBER;
   err_code    VARCHAR2 (200);
   err_msg     VARCHAR2 (200);
BEGIN
   /*Author: Alper Özsöyler */

   FOR cur_object IN (  SELECT owner, object_name, object_type

                          FROM dba_objects
                         WHERE status = 'INVALID'
                      ORDER BY owner)
   LOOP
      str_exec := 'ALTER ';

      IF cur_object.object_type = 'PACKAGE BODY'

      THEN
         str_exec := str_exec || 'PACKAGE ';
      ELSE
         str_exec := str_exec || cur_object.object_type || ' ';
      END IF;

      str_exec :=

            str_exec
         || cur_object.owner
         || '.'
         || cur_object.object_name
         || ' COMPILE ';


      IF cur_object.object_type = 'PACKAGE BODY'

      THEN
         str_exec := str_exec || 'BODY ';
      END IF;

      BEGIN

         EXECUTE IMMEDIATE str_exec;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  cur_object.owner
               || '.'
               || cur_object.object_name
               || ' ('
               || cur_object.object_type
               || ') '
               || '-> still invalid!');

            err_code := SQLCODE;

            err_msg := SUBSTR (SQLERRM, 1, 200);

            INSERT INTO t_cmp_invalid_objects_log

                 VALUES (s_cpl_inv_objects_error_log.NEXTVAL,
                         cur_object.object_name,
                         cur_object.owner,
                         cur_object.object_type,
                         err_code || ' -ERROR- ' || err_msg);

            COMMIT;

      END;
   END LOOP;

   str_exec := 'select count(*) FROM dba_objects

                       WHERE status = ''INVALID''';

   EXECUTE IMMEDIATE str_exec INTO total_inv;


   DBMS_OUTPUT.put_line ('Total invalid number: ' || total_inv);

END;
/
DROP TABLE t_cmp_invalid_objects_log
/
CREATE TABLE t_cmp_invalid_objects_log
(
   log_id          INT PRIMARY KEY,
   object_name     VARCHAR2 (100),
   object_schema   VARCHAR2 (100),
   object_type     VARCHAR2 (100),
   error           VARCHAR2 (1000)
)
/
DROP SEQUENCE s_cpl_inv_objects_error_log
/
CREATE SEQUENCE s_cpl_inv_objects_error_log MINVALUE 0
                                            START WITH 0
                                            INCREMENT BY 1
                                            CACHE 5;

Usage:

exec P_COMPILE_INVALID_OBJECTS();

Check error logs:
SQL> show error;
or
SQL> SELECT *
  FROM ALL_ERRORS
 WHERE OWNER = USER;