4 Replies Latest reply on Jul 1, 2016 12:47 PM by John_K

    invalid objects

    2907350

      EBSR12.2.4

      Oracle Linux 6.5

      Oracle Database 11.2.0.3

       

      Hello,

       

      I am compiling invalid objects using :

      SET SERVEROUTPUT ON SIZE 1000000

      BEGIN

        FOR cur_rec IN (SELECT owner,

        object_name,

        object_type,

        DECODE(object_type, 'PACKAGE', 1,

        'PACKAGE BODY', 2, 2) AS recompile_order

        FROM dba_objects

        WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')

        AND status != 'VALID'

        ORDER BY 4)

        LOOP

        BEGIN

        IF cur_rec.object_type = 'PACKAGE' THEN

        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||

        ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';

        ElSE

        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||

        '"."' || cur_rec.object_name || '" COMPILE BODY';

        END IF;

        EXCEPTION

        WHEN OTHERS THEN

        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||

        ' : ' || cur_rec.object_name);

        END;

        END LOOP;

      END;

      /

       

      but some packages cannot be compiled. If I compile them individually, they will complete with errors. Is there a way I can recover them?

       

      Regards,

      Joe