Creato da pastuweb.com
Share My Page
My Social Accounts
Account FaceBook Account LinkedIn Account Twitter Account Google Plus Account Git Hub

Here there si the procedure:

CREATE OR REPLACE PROCEDURE <user>.COMPILE_INVALID_OBJECTS IS
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type,
                                'PACKAGE',
                                1,
                                'PACKAGE BODY',
                                2,
                                2) AS recompile_order
                    FROM all_objects
                   WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'TRIGGER',
                          'PROCEDURE', 'FUNCTION ', 'VIEW')
                     AND status = 'INVALID'
                     AND owner not in ('SYS', 'SYSTEM')
                   ORDER BY 4) LOOP
    BEGIN
    
      If cur_rec.object_type in
         ('PACKAGE', 'TRIGGER', 'PROCEDURE', 'FUNCTION ', 'VIEW') 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;
/

 

Average (1 Vote)
The average rating is 5.0 stars out of 5.