Saturday, 20 May 2017

How to compile an invalid views and triggers by using dynamic sql in oracle ?

--TO COMPILE THE INVALID VIEWS IN APPS&XXEC SCHEMA'S.

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'VIEW'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'VIEW' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      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;
/

--TO COMPILE THE INVALID TRIIGERS IN APPS&XXFIN SCHEMAS

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'TRIGGER'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'TRIGGER' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      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;
/

No comments:

Post a Comment

Query to find request set and its responsibility

  SELECT FA.application_name,        fr.responsibility_name program_attached_to,        frg.request_group_name,        fcp.request_set_name,...