Re: invalid packages

Stefan Keller wrote:
>
> hi
>
> if I make changes in the structure of a table, and PL/SQL-Function
> reads this table, the function gets the status ‘invalid’.
> Does a package contain this function, the whole package is ‘invalid’.
> In this case other functions in the same package are invalid.
> What can I do or do I have to recompile the packages always when I
> make changes on one of those tables?
>
> answer to Stefan.Keller_at_oiz.stzh.ch

I've found that DBMS_UTILITY.COMPILE_SCHEMA doesn't always work. I've used it
and wound up with almost all my objects invalid.

The following script has been used successfully by myself on 7.2 and 7.3.
It first gets a list of all invalid objects and begins compiling them. Before
each compile, it checks to see if the object is still invalid because some objects may be recompiled
due to being used by an object already compiled. It will do this up to three times and then will
output a list of any objects which are still invalid.

In order to use this, you will need the proper "ALTER ANY ..." privileges.

SET SERVEROUTPUT ON

--
--******************************************************************************
--*   SOURCE=COMPILE_OBJECTS.SQL
--*   VERSION=01.01.001
--*   DESCRIPTION=This script will compile all INVALID objects and
report those
--*               which still have errors.
--*
--*   CHANGE HISTORY:
--*   Date        Name            Version     Description
--*   --------    -------------   ---------  
----------------------------------
--*   07/07/97    Alton Ayers     01.01.001   Initial release.
--*
--******************************************************************************
--
DECLARE
--
TYPE  name_type        IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
--
owner_tbl              name_type;
type_tbl               name_type;
name_tbl               name_type;
--
v_num_objs             BINARY_INTEGER;
--
v_sql                  VARCHAR2(200);
v_cursor               INTEGER;
v_return               INTEGER;
--
v_times                NUMBER;
--
BEGIN
    dbms_output.enable(100000);
    --
    v_times := 0;
    LOOP
        v_num_objs := 0;
        FOR i IN (SELECT owner, object_type, object_name
                    FROM dba_objects
                   WHERE owner NOT IN ('SYS','SYSTEM')
                     AND status = 'INVALID'
                   ORDER BY owner, object_type, object_name) LOOP
            --
            v_num_objs := v_num_objs + 1;
            owner_tbl(v_num_objs) := i.owner;
            type_tbl(v_num_objs) := i.object_type;
            name_tbl(v_num_objs) := i.object_name;
            --
        END LOOP;
        --
        EXIT WHEN v_num_objs = 0;
        --
        FOR i IN 1..v_num_objs LOOP
            FOR o IN (SELECT status
                        FROM dba_objects
                       WHERE object_type = type_tbl(i)
                         AND object_name = name_tbl(i)
                         AND owner = owner_tbl(i)) LOOP
                --
                IF o.status = 'INVALID' THEN
                    IF type_tbl(i) = 'PACKAGE BODY' THEN
                        v_sql := 'ALTER PACKAGE
'||owner_tbl(i)||'.'||name_tbl(i)||' COMPILE BODY';
                    ELSE
                        v_sql := 'ALTER '||type_tbl(i)||'
'||owner_tbl(i)||'.'||name_tbl(i)||' COMPILE';
                    END IF;
                    v_cursor := dbms_sql.open_cursor;
                    dbms_sql.parse(v_cursor, v_sql, dbms_sql.V7);
                    BEGIN
                        v_return := dbms_sql.execute(v_cursor);
                    EXCEPTION
                        WHEN OTHERS THEN
                            NULL;
                    END;
                    dbms_sql.close_cursor(v_cursor);
                END IF;
                --
            END LOOP;
        END LOOP;
        --
        v_times := v_times + 1;
        --
        EXIT WHEN v_times = 3;
        --
    END LOOP;
    --
    IF v_num_objs > 0 THEN
        dbms_output.put_line(CHR(10)||RPAD('*',60,'*')||CHR(10)||'*');
        dbms_output.put_line('*  THE FOLLOWING OBJECTS REMAIN
INVALID!    '||CHR(10)||'*');
        FOR i IN (SELECT owner, object_type, object_name
                    FROM dba_objects
                   WHERE owner NOT IN ('SYS','SYSTEM')
                     AND status = 'INVALID'
                   ORDER BY owner, object_type, object_name) LOOP
            --
            dbms_output.put_line('*   
'||RPAD(i.object_type,16)||i.owner||'.'||i.object_name);
            --
        END LOOP;
        dbms_output.put_line('*'||CHR(10)||RPAD('*',60,'*')||CHR(10));
    ELSE
        dbms_output.put_line(CHR(10)||RPAD('*',60,'*')||CHR(10)||'*');
        dbms_output.put_line('*    ALL OBJECTS COMPILED SUCESSFULLY!');
        dbms_output.put_line('*'||CHR(10)||RPAD('*',60,'*')||CHR(10));
    END IF;
END;
/