CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
user1008697
user1008697

Reputation: 1151

Oracle - Excluding certain packages from recompiling using DBMS_UTILITY.compile_schema

Is it possible to exclude certain packages from the DBMS_UTILITY.compile_schema command? Even though setting the parameter to FALSE only recompiles invalid objects, I want to be sure that certain packages are forever excluded to prevent dependencies errors.

Upvotes: 0

Views: 242

Answers (1)

TenG
TenG

Reputation: 4004

One solution is to use a script to generate the compiles exclduing the ones. E.g.

DECLARE
  CURSOR c1 IS 
    SELECT object_name, object_type 
    FROM user_objects
    WHERE status = 'INVALID'
    AND   object_name NOT IN ( 'EXCL_OBJ1', .... );
  cmd  VARCHAR2 (1000);
BEGIN
  FOR r1 IN c1 LOOP
    -- You will need logic here to cater for different 
    -- command e.g. PACKAGE and PACKAGE BODY
    cmd := 'ALTER ' || r1.object_type || ' ' || r1.object_name || ' compile';
    EXECUTE IMMEDIATE cmd;
  END LOOP;
END;

I usually have a repeat loop to count the invalid objects and exit when the count is the same for 2 consecutive loops.

Note that you will need some logic to cater for different compile command syntax between PACKAGE headers and bodies.

Upvotes: 2

Related Questions