How to recompile invalid objects recursively

About recom­pil­ing invalid objects in Ora­cle data­base on the Inter­net, there is cer­tainly lots of scripts. Still, I decided to write my own pack­age, which can skip recom­pil­ing addi­tion of objects, which are unde­sir­able in that moment, or com­pile them for a long time “hangs” (I mean for exam­ple, objects that con­tain links to remote Data­base (db link) and this is not avail­able at the moment — then com­pile a long time to stop and wait for a response from the remote data­base. It hap­pens often in the devel­op­ment instances)

[Česká verze článku — Jak rekurzivně rekom­pilo­vat invalidní objekty]

UPDATE 20.08.2010 - Of course the eas­i­est way to recom­pile invalid objects is to use the sys­tem pack­age UTL_RECOMP a PROCEDURE RECOMP_SERIAL so: “exec UTL_RECOMP.RECOMP_SERIAL(schema => ‘MY_SCHEMA_NAME’);”. My solu­tion only adds to omit some of the objects of recom­pi­la­tion. It is nicely described on the server psoug.org

And now for my solu­tion. First, you cre­ate a table in which we store the objects that we want to omit from the com­pi­la­tion.

-- Create a table of objects that do not want to compile.
CREATE TABLE UTL$DB_COMPILE_OBJ_IGNORE
(
  ID                               NUMBER           NOT NULL,
  SCHEMA_NAME                      VARCHAR2(30)     NOT NULL,
  OBJECT_NAME                      VARCHAR2(30)     NOT NULL,
  CONSTRAINT UTL$DB_COMPILE_OBJ_IGNORE_PK  PRIMARY KEY ( ID )
    USING INDEX
  ENABLE VALIDATE
)
/

-- Create index
CREATE INDEX I_UTL$DB_COMPILE_OBJ_IGNORE
  ON UTL$DB_COMPILE_OBJ_IGNORE
  (
   SCHEMA_NAME,
   OBJECT_NAME
  )
/

-- Create sequence to generate primary key
CREATE SEQUENCE UTL$DB_COMPILE_OBJ_IGNORE_SEQ
/

-- Create trigger
CREATE OR REPLACE TRIGGER UTL$DB_COMPILE_OBJ_IGNORE_ID
  BEFORE INSERT ON UTL$DB_COMPILE_OBJ_IGNORE FOR EACH ROW
BEGIN
    IF(:NEW.ID IS NULL) THEN
        SELECT UTL$DB_COMPILE_OBJ_IGNORE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END;
/
COMMENT ON TABLE UTL$DB_COMPILE_OBJ_IGNORE IS 'Table of objects that do not want to compile'
/
COMMENT ON COLUMN UTL$DB_COMPILE_OBJ_IGNORE.ID IS 'Primary key'
/
COMMENT ON COLUMN UTL$DB_COMPILE_OBJ_IGNORE.OBJECT_NAME IS 'Object name'
/
COMMENT ON COLUMN UTL$DB_COMPILE_OBJ_IGNORE.SCHEMA_NAME IS 'Owner of this object'
/

Then, cre­ate a pack­age that will con­tain the pro­ce­dure compile_objects, it will be the one that will recom­pile objects recur­sively. Pack­age is placed for test­ing pur­poses in schema “SCOTT” and name it as UTL$DB.

Remem­ber to adjust the con­stants c_SELF_SCHEMA_NAME by scheme, in which the pack­age will be installed! In our case, this scheme is ‘SCOTT’!
CREATE OR REPLACE PACKAGE UTL$DB
IS

    /**
    * ======================================================================
    *                               ANNOTATION
    * ======================================================================
    *  Base package to recursively recompile invalid objects
    * ----------------------------------------------------------------------
    * Autor: Martin Mareš
    * Copyright (C) 2010 Martin Mareš, All rights reserved... but anybody can use it :)
    * ======================================================================
    */
    c_LINE_LENGTH      CONSTANT NUMBER := 60;
    c_SELF_SCHEMA_NAME CONSTANT VARCHAR2(50) := 'SCOTT';  -- don't compile itself
    c_SELF_OBJECT_NAME CONSTANT VARCHAR2(50) := 'UTL$DB';  -- don't compile itself
    e_ALL_EXCEPTIONS   CONSTANT NUMBER := - 20001;

    TYPE struct_object IS RECORD (
        schema_name UTL$DB_COMPILE_OBJ_IGNORE.SCHEMA_NAME%TYPE,
        object_name UTL$DB_COMPILE_OBJ_IGNORE.OBJECT_NAME%TYPE,
        object_type VARCHAR2(200)
    );

    TYPE list_objects IS TABLE OF struct_object INDEX BY BINARY_INTEGER;

    PROCEDURE compile_objects(schema_name_in  IN VARCHAR2 DEFAULT NULL
                             ,object_name_in  IN VARCHAR2 DEFAULT NULL
                             ,show_skipped_in IN VARCHAR2 DEFAULT 'N');

END UTL$DB;
/
CREATE OR REPLACE PACKAGE BODY UTL$DB
IS
    FUNCTION construct_compile_command (schema_name_in IN VARCHAR2
                                       ,object_name_in IN VARCHAR2
                                       ,object_type_in IN VARCHAR2) RETURN VARCHAR2
    IS
        l_return_compile_comm VARCHAR2(2000);

    BEGIN
        l_return_compile_comm := 'ALTER ' ||

            CASE
                WHEN object_type_in IN('PACKAGE', 'PACKAGE BODY')
                    THEN 'PACKAGE'
                WHEN object_type_in IN('JAVA CLASS', 'JAVA SOURCE')
                    THEN 'JAVA CLASS'
                ELSE object_type_in
            END ||
                                 ' "' ||
                                 schema_name_in ||
                                 '"."' ||
                                 object_name_in ||
                                 '"' ||

            CASE
                WHEN object_type_in IN('JAVA CLASS', 'JAVA SOURCE')
                    THEN ' RESOLVE'
                ELSE ' COMPILE ' ||

                    CASE
                        WHEN object_type_in IN('PACKAGE BODY')
                            THEN 'BODY'
                        ELSE NULL
                    END
            END;
        RETURN l_return_compile_comm;
    END construct_compile_command;

    FUNCTION get_list_objects(schema_name_in  IN VARCHAR2 DEFAULT NULL
                             ,object_name_in  IN VARCHAR2 DEFAULT NULL
                             ,show_skipped_in IN VARCHAR2 DEFAULT 'N') RETURN list_objects
    IS
        l_struct_object struct_object;
        l_list_objects  list_objects;

    BEGIN

        <<list_all_objects>>
        FOR cur_objects IN  (  SELECT obj.owner AS schema_name
                                     ,obj.object_name AS object_name
                                     ,obj.object_type AS object_type
                                 FROM all_objects obj
                                WHERE obj.status <> 'VALID'
                                  AND NOT (obj.owner = c_SELF_SCHEMA_NAME
                                  AND obj.object_name = c_SELF_OBJECT_NAME)  -- don't compile itself
                                  AND obj.object_type IN('TRIGGER', 'PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'JAVA CLASS', 'JAVA SOURCE')
                                  AND obj.owner = COALESCE(schema_name_in, obj.owner)
                                  AND obj.object_name = COALESCE(object_name_in, obj.object_name)
                             ORDER BY DECODE(obj.object_type,
                                      'JAVA SOURCE', 1,
                                      'JAVA CLASS', 2,
                                      'PACKAGE', 3,
                                      'PACKAGE BODY', 4,
                                      'PROCEDURE', 5,
                                      'FUNCTION', 6,
                                      'TRIGGER', 7,
                                      'VIEW', 8,
                                      10) ) LOOP
            l_struct_object.schema_name := cur_objects.schema_name;
            l_struct_object.object_name := cur_objects.object_name;
            l_struct_object.object_type := cur_objects.object_type;
            l_list_objects(l_list_objects.COUNT) := l_struct_object;
        END LOOP list_all_objects;

        <<objects_not_necessary>>
        FOR cur_objects IN  (SELECT schema_name
                                   ,object_name
                               FROM utl$db_compile_obj_ignore ) LOOP

            IF (l_list_objects.COUNT > 0) THEN

                <<loop_all_objects>>
                FOR i IN l_list_objects.FIRST..l_list_objects.LAST LOOP

                    IF (l_list_objects.EXISTS(i)) THEN
                        l_struct_object := l_list_objects(i);

                        IF l_struct_object.schema_name LIKE cur_objects.schema_name
                           AND l_struct_object.object_name LIKE cur_objects.object_name THEN
                            l_list_objects.DELETE(i);

                            IF (UPPER(show_skipped_in) IN('Y'
                                                         ,'YES'
                                                         ,'A'
                                                         ,'ANO')) THEN
                                DBMS_OUTPUT.put_line('Skip ' ||
                                                     l_struct_object.schema_name ||
                                                     '.' ||
                                                     l_struct_object.object_name ||
                                                     ' (Pattern: ' ||
                                                     cur_objects.schema_name ||
                                                     '/' ||
                                                     cur_objects.object_name ||
                                                     ')');
                            END IF;

                        END IF;

                    END IF;

                END LOOP loop_all_objects;
            END IF;

        END LOOP objects_not_necessary;

        RETURN l_list_objects;
    END get_list_objects;

    PROCEDURE compile_objects(schema_name_in  IN VARCHAR2 DEFAULT NULL
                             ,object_name_in  IN VARCHAR2 DEFAULT NULL
                             ,show_skipped_in IN VARCHAR2 DEFAULT 'N')
    IS
        l_compile_command VARCHAR2(2000);
        l_struct_object   struct_object;
        l_list_objects    list_objects;
        l_count_first     NUMBER;
        l_count_before    NUMBER;
        l_count_after     NUMBER;

    BEGIN
        l_list_objects := get_list_objects(schema_name_in => schema_name_in,
                                           object_name_in => object_name_in,
                                           show_skipped_in => show_skipped_in);
        l_count_first := l_list_objects.COUNT;
        l_count_before := l_count_first;
        l_count_after := 0;

        WHILE(l_count_before <> l_count_after) LOOP
            l_count_before := l_list_objects.COUNT;

            IF (l_list_objects.COUNT > 0) THEN
                FOR i IN l_list_objects.FIRST..l_list_objects.LAST LOOP

                    IF (l_list_objects.EXISTS(i)) THEN
                        l_struct_object := l_list_objects(i);
                        l_compile_command := construct_compile_command (schema_name_in => l_struct_object.schema_name
                                                                       ,object_name_in => l_struct_object.object_name
                                                                       ,object_type_in => l_struct_object.object_type);

                        BEGIN
                            EXECUTE IMMEDIATE l_compile_command ;
                            EXCEPTION
                                WHEN OTHERS THEN

                                    IF (SQLCODE NOT IN( - 24344
                                                      , - 4052
                                                      , - 1031)) THEN
                                        DBMS_OUTPUT.put_line('SQLCODE = ' ||
                                                             SQLCODE);
                                        DBMS_OUTPUT.put_line('SQLERRM = ' ||
                                                             SQLERRM);
                                        DBMS_OUTPUT.put_line('Try Execute = ' ||
                                                             l_compile_command);
                                        RAISE_APPLICATION_ERROR (e_ALL_EXCEPTIONS
                                                                ,'Exception in execute immediate command (' ||
                                                                 sqlerrm ||
                                                                 ').');
                                    END IF;

                        END;
                    END IF;

                END LOOP;
            END IF;

            l_list_objects := get_list_objects(schema_name_in => schema_name_in,
                                               object_name_in => object_name_in);
            l_count_after := l_list_objects.COUNT;
        END LOOP;

        IF (l_list_objects.COUNT > 0) THEN
            DBMS_OUTPUT.put_line(RPAD('-- [Invalid objects after compilation] ',
                                      c_LINE_LENGTH, '-'));

            FOR i IN l_list_objects.FIRST..l_list_objects.LAST LOOP

                IF (l_list_objects.EXISTS(i)) THEN
                    l_struct_object := l_list_objects(i);
                    DBMS_OUTPUT.put_line(l_struct_object.schema_name ||
                                         '.' ||
                                         l_struct_object.object_name ||
                                         ' (' ||
                                         l_struct_object.object_type ||
                                         ')');
                END IF;

            END LOOP;

        END IF;

        DBMS_OUTPUT.put_line(RPAD('-- [Summary] ', c_LINE_LENGTH, '-'));
        DBMS_OUTPUT.put_line('Invalid objects before compilation = ' ||
                             l_count_first);
        DBMS_OUTPUT.put_line('Invalid objects after compilation = ' ||
                             l_count_after);
    END compile_objects;

END UTL$DB;
/

Now we will show how to use the pro­ce­dure compile_objects(). Our table UTL$DB_COMPILE_OBJ_IGNORE con­tains the fol­low­ing objects. These objects will be omit­ted from the compilation.

ID SCHEMA_NAME OBJECT_NAME
1 SYS %
2 SYSTEM %
3 WMSYS %
4 ADMIN %
5 ADMIN %OMIT_PACKAGE%

If you run the pro­ce­dure compile_objects() follows:

SQL> exec scott.utl$db.compile_objects();

…appears, for exam­ple (do not for­get to turn on dbms_output throught “set server­out­put on” in SQL*Plus):

-- [Invalid objects after compilation] ---------------------
TESTSCHEMA.SYPK_FLOW (PACKAGE)
TESTSCHEMA.SYPK_TEST (PACKAGE)
TESTSCHEMA.SYPK_MAIN (PACKAGE)
-- [Summary] -----------------------------------------------
Invalid objects before compilation = 3
Invalid objects after compilation = 3

This means that some objects could not com­pile. Find and fix a bug in the test pack­age “TESTSCHEMA.SYPK_FLOW” and re-run compile_objects() the state­ment see:

-- [Summary] -----------------------------------------------
Invalid objects before compilation = 3
Invalid objects after compilation = 0

If I run compile_objects() with optional para­me­ters such:

SQL> exec scott.utl$db.compile_objects(NULL, NULL, 'Y');

In the list­ing it will appear objects that have been omit­ted from the compilation:

Skip ADMIN.TEST_PACKAGE (Pattern: ADMIN/%)
Skip ADMIN.TEST_PACKAGE2 (Pattern: ADMIN/%)
Skip ADMIN.TEST_PACKAGE3 (Pattern: ADMIN/%)
Skip ADMIN.OMIT_PACKAGE1 (Pattern: ADMIN/%OMIT_PACKAGE%)
Skip ADMIN.OMIT_PACKAGE2 (Pattern: ADMIN/%OMIT_PACKAGE%)
Skip ADMIN.OMIT_PACKAGE3 (Pattern: ADMIN/%OMIT_PACKAGE%)
-- [Summary] -----------------------------------------------
Invalid objects before compilation = 0
Invalid objects after compilation = 0

Related posts:

  1. Pro­gram for recom­pil­ing invalid pack­ages — by Sow­janya Bhupathiraju

Post to Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>