删除 pragma

DECLARE
   stmt_ CLOB;
   
   CURSOR get_pkg IS
      SELECT OBJECT_NAME
        FROM USER_OBJECTS
       WHERE OBJECT_TYPE = 'PACKAGE BODY'
         AND STATUS = 'INVALID';
         
   CURSOR get_rec(name_ VARCHAR2) IS
      SELECT TEXT
        FROM USER_SOURCE
       WHERE NAME = name_
         AND TYPE = 'PACKAGE'
       ORDER BY LINE;
BEGIN
   FOR pkg_ IN get_pkg LOOP
      BEGIN 
         stmt_ := 'CREATE OR REPLACE ';
         FOR rec_ IN get_rec(pkg_.OBJECT_NAME) LOOP
            IF UPPER(rec_.text) NOT LIKE 'PRAGMA RESTRICT_REFERENCES%' THEN
               stmt_ := stmt_ || rec_.text;
            END IF;
         END LOOP;
         EXECUTE IMMEDIATE stmt_;
         
         stmt_ := 'ALTER PACKAGE ' || pkg_.OBJECT_NAME || ' COMPILE';
         EXECUTE IMMEDIATE stmt_;
         
         stmt_ := 'ALTER PACKAGE ' || pkg_.OBJECT_NAME || ' COMPILE BODY';
         EXECUTE IMMEDIATE stmt_;
      EXCEPTION 
         WHEN OTHERS THEN 
            NULL;
      END;   
   END LOOP;
END;
/