Compiling Oracle objects in right order

Hi

I have a scenario like i have around 100 invalid objects in my system and i need to recompile them in one shot.
Is there any method by which this can be done with a condition that objects should be recompiled in a manner that..compilation of one object should not invalidate any other object in my database.

suhinrasheedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prasanthi_kCommented:
Here is a script that compiles all invalid objects...
If there are any links between 2 objects, then 1st object can invalidate the second object. Otherwise validating an object will not make any other object invalid.

set heading off
set feedback off
spool x.dat
select 'alter ' || decode(object_type,'PACKAGE
BODY','PACKAGE',object_type)
|| ' ' || object_name || ' compile '  ||
decode(object_type,'PACKAGE BODY',' body;',';')  from user_objects
where object_type in ('FUNCTION','PACKAGE','PACKAGE
BODY','PROCEDURE','TRIGGER','VIEW')
and status = 'INVALID'
order by object_type , object_name;
spool off
 
set heading on
set feedback on
@x.dat  

0
suhinrasheedAuthor Commented:
What did yu mean by anylinks between 2 objects?
0
sonicefuCommented:

SET SERVEROUTPUT ON SIZE 1000000
 
BEGIN
   FOR cur_rec IN (SELECT   owner, object_name, object_type,
                            DECODE (object_type,
                                    'PACKAGE', 1,
                                    'PACKAGE BODY', 2,
                                    3
                                   ) AS recompile_order
                       FROM dba_objects
                      WHERE object_type IN
                               ('PACKAGE',
                                'PACKAGE BODY',
                                'FUNCTION',
                                'PROCEDURE'
                               )
                        AND status != 'VALID'
                   ORDER BY 4)
   LOOP
      BEGIN
         IF cur_rec.object_type ('FUNCTION', 'PROCEDURE')
         THEN
            EXECUTE IMMEDIATE    'ALTER '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.owner
                              || '"."'
                              || cur_rec.object_name
                              || '" COMPILE';
         ELSIF 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;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

prasanthi_kCommented:
Suppose 1 procedure calls another procedure in its code.
Suppose 2nd procedure has some problem (for eg: column names written there are modified later in the table) and is not compiled, then automatically the 1st procedure is also invalidated.
0
suhinrasheedAuthor Commented:
Prashanth ..so i would prefer a script which takes care of all such scenarios you mentioned before.

That is my if my recompilation script is validating package 2 and during that recopilation of package 2..package 1 which was recompiled before becomes invalidated..i want my script to handle such a scenario and validate package1

So the compilation script should work in such a manner that objects are validated in the corect order
0
prasanthi_kCommented:
As far as I know such a script is not possible...

See these scenarios for example :

1. Package-1 has a column name 'Employee_name' in its code from table 'Employee' and is a valid package. On the object 'Employee' the column name is altered as 'Emp_name' instead of 'Employee_name'

Now Package-1 becomes 'INVALID'. whether it is compiled before table 'Employee' or after that. It can be compiled properly only when the column name in its code is changed accordingly as in the table... (This is just an example, the problem behind the object not getting validated can be anything that needs to be found...)

2. Package-2 calls a function in 'Package-1'. Now as 'Package-1' is INVALID, in what ever order it is compiled 'Package-2' should become 'INVALID' as it has got a problem. So irrespective of the order, the Invalid packages cannot be made 'VALID' unless the issue with them is corrected.
0
suhinrasheedAuthor Commented:
but i have seen scenarios in whch say i had to recompile a package and once i recompile it some of the dependent objects becomes invalid,athough these dependent objects are recompiled and validated automatically by oracle the next time they are invoked...My qstn is it not possible to do that recompilation of all dependent objects the same time the base object is recompiled
0
prasanthi_kCommented:
What is the current method you are following to compile the package?
Try following the above given scripts that will compile all the objects at a shot...
0
suhinrasheedAuthor Commented:
Prashanth will the above script ensure that the objects are recompiled in the correct manner
0
suhinrasheedAuthor Commented:
Sonice can you give me some brief witten explanation what your script exactly does.. i mean part by part
0
jwittenmCommented:
Have you used UTLRP?
0
sonicefuCommented:
my code gets invalid package, package body, functions and procedure names and then compile them all in the following order
1. PACKAGE
2. PACKAGE BODY
3. Functions and procedures
0
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.